August 21, 2019
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 !
Trusted Members
Moderators
January 31, 2022
Trusted Members
Moderators
January 31, 2022
Trusted Members
Moderators
January 31, 2022
Trusted Members
Moderators
November 1, 2018
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)