Forum Discussion

alex's avatar
alex
Helper I
7 years ago
Solved

Move Sheet Titles to Rows ( Power Query )

I commonly run into source data XLS formated liked so: Sheet Title1;                          ;Col.Title1        ; Col.Title 2   ; Col.Title 3   ; Col.Title 4  RowTitle1          ; Value          ...
Need Help
  • DaFloDo's avatar
    DaFloDo
    7 years ago

    hi alex,

     

    this is how I achieved the result ...

     

     

    you'll have to make some adjustments to fit your needs, but in general it should be possible ... here is the code to get you started:

    let
    Quelle = Excel.Workbook(File.Contents("pathtoexcelfile.xlsx"), null, true),
    Tabelle1 = Quelle{[Item="Tabelle1",Kind="Sheet"]}[Data],
    SheetTitle = List.First(Tabelle1[Column1]),
    tbl2 = Table.RemoveRows(Tabelle1, 0),
    tbl3 = Table.PromoteHeaders(tbl2, [PromoteAllScalars=true]),
    tbl4 = Table.RenameColumns(tbl3,{{"Column1", "RowTitle"}}),
    tbl5 = Table.AddColumn(tbl4, "SheetTitle", each SheetTitle, type text)
in
    tbl5

     

     

    best regards

     

    florian

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Please, can you try writing an excel macro to do this activity for one sheet and then re-use it for other sheets. Not aware of any fix through Power BI.

 

The other option is - instead of using the pivot table as the source, please try using the source of the pivot table as source and then cleanse / transform/model with Power BI. I prefer this one personally.

alex's avatar
alex
Helper I
7 years ago

No, i want a Power Query solution.....

...the sheets get updated by others and a Macro would be a pain.

...and  they're just cells in a sheet, not pivot tables made from source data....