Forum Discussion
chaichatp
Helper III
8 years agoAppend Rows using Another columns
I have tried to append rows of by cloumns, the original data looks like this Name Date1 Cost1 Date2 Cost2 A 2013-03-25 1,923,245.06 B 2015-06-04 4,104,660.00 ...
- 8 years ago
This should work:
let Source = Excel.CurrentWorkbook(){[Name="Test_Unpivot"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {" Date1 ", type datetime}, {" Cost1 ", type text}, {"Date2", type datetime}, {" Cost2 ", type text}}), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {" Date1 ", " Cost1 ", "Date2", " Cost2 "}, "Attribut", "Value"), #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Only Selected Columns",{{"Attribut", Text.Trim, type text}}), #"Added Name_temp" = Table.AddColumn(#"Trimmed Text", "Name_temp", each [Name]&Text.End([Attribut],1)), #"Replace '1'" = Table.ReplaceValue(#"Added Name_temp","1","",Replacer.ReplaceText,{"Attribut"}), #"Replace '2'" = Table.ReplaceValue(#"Replace '1'","2","",Replacer.ReplaceText,{"Attribut"}), #"Removed Name" = Table.RemoveColumns(#"Replace '2'",{"Name"}), #"Pivoted Column" = Table.Pivot(#"Removed Name", List.Distinct(#"Removed Name"[Attribut]), "Attribut", "Value"), #"Add Name" = Table.AddColumn(#"Pivoted Column", "Name", each Text.Start([Name_temp],1)), #"Removed Name_temp" = Table.RemoveColumns(#"Add Name",{"Name_temp"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Name_temp",{"Name", "Date", "Cost"}), #"Changed Date" = Table.TransformColumnTypes(#"Reordered Columns",{{"Date", type date}}) in #"Changed Date"
mmace1
Impactful Individual
8 years agoNot very fancy - but, what about pulling in your data twice?
First query - just grab the left most data (remove the columns to the right.)
Second query - just grab the right most data (remove the columns on the left)
Or vise versa.
Then merge those 2 queries together.