Log In
Register
Home
August 21, 2019
Offline
Hi PQ Query,
I tried to apply List.Combine () but the 2nd list append to the first list, and I wanted the column 1 and column 2 to be side by side in a field and when I expand, it should be correspond to each other (correct me if I am wrong)
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Custom1 = Table.TransformColumns( Source,
{{"Name", each Text.Split(_, ",")},
{"Department", each Text.Split(_, ",")}
}
),
#"Added Custom" = Table.AddColumn(Custom1, "Custom", each List.Combine({[Name],[Department]}))
in
#"Added Custom"
Thank you very much !
See Answer
Moderators
January 31, 2022
Offline
After splitting, transpose the table and then Table.FromColumns.
See attached in the Table1 query.
August 21, 2019
Offline
Hi Riny,
thanks for your quick response, my sample is only one record (original source got few hundred), and how to handle multiple records using your Mcode
Note: added a second record in the attachment
thank you Sir
Moderators
January 31, 2022
Offline
Then I would opt for creating two separate tables. One for Names and one for Departments and merge them on the basis of an index column. Perhaps not the most sophisticated solution, but it works. See the Merge1 query attached.
Moderators
January 31, 2022
Offline
Added another possible solution. See Custom2 query in the attached file.
Trusted Members
Moderators
November 1, 2018
Offline
You could also use List.Zip and then Table.FromRows:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Custom1 = Table.TransformColumns( Source, {{"Name", each Text.Split(_, ",")}, {"Department", each Text.Split(_, ",")}}),
#"Added Custom" = Table.AddColumn(Custom1, "Custom", each List.Zip({[Name], [Department]})),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.FromRows([Custom])),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Column1", "Column2"}, {"Custom.1.Column1", "Custom.1.Column2"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"Name", "Department", "Custom"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Custom.1.Column1", Text.Trim, type text}, {"Custom.1.Column2", Text.Trim, type text}})
in
#"Trimmed Text"
Answers Post
1 Guest(s)
