Forum Discussion

chaichatp's avatar
chaichatp
Icon for Helper III rankHelper III
8 years ago
Solved

Append 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 ...
Need Help
  • Floriankx's avatar
    Floriankx
    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"
Floriankx's avatar
Floriankx
Icon for Solution Sage rankSolution Sage
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"
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    chaichatp

     

    Also, you can create a Calculated Table in DAX to acheive desired output

     

    From the Modelling Tab>>New Table

     

    New Table =
VAR tbl1 =
    SELECTCOLUMNS ( Table1, "Name", [Name], "Date", [ Date1 ], "Cost", [ Cost1 ] )
VAR tbl2 =
    SELECTCOLUMNS ( Table1, "Name", [Name], "Date", [Date2], "Cost", [ Cost2 ] )
RETURN
    UNION ( tbl1, tbl2 )