Transpose multiple sheets in power query

0
votes

I am trying to transform worksheets and combining the results using power query. Below are the data and expected results.

Sheet1:

enter image description here

Sheet2:

enter image description here

Power query tried:

let
    Source = Excel.Workbook(File.Contents("\\ielett12kf56\x230154$\Desktop\Test.xlsx"), null, true),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Item", "Kind", "Hidden"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Data", each ([Name] <> "Sheet3")),
    #"Transposed Table" = Table.Transpose(#"Filtered Rows1"),
    #"Filtered Rows" = Table.SelectRows(#"Transposed Table", each ([Column1] <> null))
in
    #"Filtered Rows"

Above is the power query is transposed horizontally, but i need the sheet details to be added vertically.

enter image description here

Expected result is as below.

enter image description here

2
powerbipowerquery
Do you also required row 6 and 7 as shown in your expected output? – mkRabbani

2 Answers

1
votes

You should use unpivot and appending of tables. Below the full script:

let
Source = Excel.Workbook(File.Contents("C:\Users\snlaln\OneDrive -SAS\Documents\test.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
#"Changed Type1" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}}),
#"Changed Type2" = Table.TransformColumnTypes(Sheet2_Sheet,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}}),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Changed Type1", {"Column1"}, "ColumnIndex", "Value"),
#"Unpivoted Columns2" = Table.UnpivotOtherColumns(#"Changed Type2", {"Column1"}, "ColumnIndex", "Value"),
#"Added Custom1" = Table.AddColumn(#"Unpivoted Columns1", "Sheet", each "Sheet1"),
#"Added Custom2" = Table.AddColumn(#"Unpivoted Columns2", "Sheet", each "Sheet2"),
#"Appended Query" = Table.Combine({#"Added Custom1", #"Added Custom2"})

in #"Appended Query"

Result below, the table lends itself do make the correct visuals.. enter image description here

1
votes

Try this below Power query script to achieve your desired output-

let
    Source = Excel.Workbook(File.Contents("D:\WORK\R&D\Book2.xlsx"), null, true),

    sheet1_Sheet = Source{[Item="sheet1",Kind="Sheet"]}[Data],
    sheet2_Sheet = Source{[Item="sheet2",Kind="Sheet"]}[Data],

    #"Unpivoted Columns" = Table.UnpivotOtherColumns(sheet1_Sheet, {"Column1"}, "Attribute", "Value"),
    #"Unpivoted Columns2" = Table.UnpivotOtherColumns(sheet2_Sheet, {"Column1"}, "Attribute", "Value"),

    #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Column1]), "Column1", "Value", List.Sum),
    #"Pivoted Column2" = Table.Pivot(#"Unpivoted Columns2", List.Distinct(#"Unpivoted Columns"[Column1]), "Column1", "Value", List.Sum),

    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Attribute"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column2",{"Attribute"}),

    #"Demoted Headers" = Table.DemoteHeaders(#"Removed Columns"),
    #"Demoted Headers2" = Table.DemoteHeaders(#"Removed Columns2"),

    #"Demoted Headers1" = Table.DemoteHeaders(#"Demoted Headers"),
    #"Demoted Headers12" = Table.DemoteHeaders(#"Demoted Headers2"),

    #"Replaced Value" = Table.ReplaceValue(#"Demoted Headers1","Column1","Sheet1",Replacer.ReplaceValue,{"Column1"}),
   #"Replaced2 Value" = Table.ReplaceValue(#"Demoted Headers12","Column1","Sheet2",Replacer.ReplaceValue,{"Column1"}),

    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Column2","Sheet1",Replacer.ReplaceValue,{"Column2"}),
    #"Replaced2 Value1" = Table.ReplaceValue(#"Replaced2 Value","Column2","Sheet2",Replacer.ReplaceValue,{"Column2"}),

    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Column3","Sheet1",Replacer.ReplaceValue,{"Column3"}),
    #"Replaced2 Value2" = Table.ReplaceValue(#"Replaced2 Value1","Column3","Sheet2",Replacer.ReplaceValue,{"Column3"}),

    #"Appended Two Sheet" = Table.Combine({#"Replaced Value2", #"Replaced2 Value2"})
in
    #"Appended Two Sheet"

Input sample data from 2 sheet-

enter image description here

Final output in Power BI-

enter image description here