Forum Discussion

musclay13's avatar
musclay13
Frequent Visitor
3 years ago
Solved

How to transform 2 sets of data with different header/table orientation in a worksheet

Hi All!  Im pretty new to Power Query and have been trying to figure this out but cant find any solution. I really hope you all can assist me with this. Background: - Assuming im creating a wo...
Need Help
  • mussaenda's avatar
    mussaenda
    3 years ago

    Hi musclay13 

     

    You are looking for this, right?

     

    you can see in the M query 2 transformations:

    One is for the header and another one for the body then I merged them to get the full view.

     

    You can even re-create the form in pbi with selection of your choice.

     

    The location of my file is in Desktop so you might want to change the location of the folder.

    I attached the pbix.

     

    Hope this helps

  • mussaenda's avatar
    mussaenda
    3 years ago

    Hi musclay13 ,

     

    I attached the sample pbix for your review.

    You can go through each step to see what is happening.

    Basically, I pulled the files via folder, transformed the sample file according to your requirement, loaded them all, and combined them.

     

    If you will see the sample pbix and change the datasource to your own datasource, 

    you will see the transformation.

     

    Hope this helps

musclay13's avatar
musclay13
Frequent Visitor
3 years ago

Anyone can help me with this? 

  • Mederic's avatar
    Mederic
    Icon for Post Patron rankPost Patron
    3 years ago

    Hello, 

    Like this ?

     

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Range"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}}),
    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Kept First Rows" = Table.FirstN(#"Removed Blank Rows",3),
    #"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Column1", "Column2"}),
    #"Transposed Table" = Table.Transpose(#"Removed Other Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    TopRowsInRecord = Table.TransformColumnTypes(#"Promoted Headers",{{"Employee ID", Int64.Type}, {"Employee Name", type text}, {"Departement", type text}}){0},
    BottomRows = #"Removed Blank Rows",
    #"Removed Top Rows" = Table.Skip(BottomRows,3),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers1", "Custom", each TopRowsInRecord),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Employee ID", "Employee Name", "Departement"}, {"Employee ID", "Employee Name", "Departement"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Date", type date}, {"TOTAL", type number}, {"Employee Name", type text}, {"Departement", type text}, {"DESCRIPTION", type text}})
in
    #"Changed Type1"

     

     

    • musclay13's avatar
      musclay13
      Frequent Visitor
      3 years ago

      Hi Mederic , Yes that is something im trying to achieve! 

      Firstly thank you for spending your precious time to create the scenario and showing me the query. 🙂 Im still quite new to Power Query.
      Do I have to type out the M language or is there steps through Power query to achieve what you did?
      Also, if I have multiple workbooks of the same form in a folder, can this be performed with "Get data from folder"?