How to add new rows which refers to other columns

[Anonymous]

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

[PhilipTreacy · Accepted 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.

[Anonymous]

Thanks Philips, that worked perfectly as I wanted 🙂

[shaowu459]

Another solution FYI.

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    res = List.RemoveNulls(List.Combine(Table.ToRows(Source)))
in
    res