Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
How can I get result column which referes to Column 1, 2 ,3
Column1 | Column2 | Column3 | Result | |
1 | 1 | |||
2 | 2 | |||
3 | a | 3 | ||
4 | b | x | a | |
5 | c | 4 | ||
6 | d | y | b | |
7 | e | z | x | |
5 | ||||
c | ||||
6 | ||||
d | ||||
y | ||||
7 | ||||
e | ||||
z |
Solved! Go to Solution.
Hi @Anonymous
The 3 columns are loaded from an Excel table called Data
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Transposed Table1" = Table.Transpose(#"Transposed Table"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Transposed Table1", {{"Column1", type text}}, "en-AU"),{"Column1", "Column2", "Column3"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Merged", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Merged] <> ""))
in
#"Filtered Rows"
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
Hi @Anonymous
The 3 columns are loaded from an Excel table called Data
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Transposed Table1" = Table.Transpose(#"Transposed Table"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Transposed Table1", {{"Column1", type text}}, "en-AU"),{"Column1", "Column2", "Column3"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Merged", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Merged] <> ""))
in
#"Filtered Rows"
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
Another solution FYI.
let
Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
res = List.RemoveNulls(List.Combine(Table.ToRows(Source)))
in
res