Move Sheet Titles to Rows ( Power Query )

[alex]

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               ; Value            ; Value            ; Value

RowTitle2          ; Value               ; Value             ; Value            ; Value

(Sheet Title1 overlaps either the Rows or the Columns usually.  It's not on it's own row.)

(also, there are multiple "tables" like this on the sheet.   (they are not actual Excel tables or PivotTables though))

Any tricks to transform to:

DummyHeader ;DummyRowHeader ;Col.Title1        ; Col.Title 2   ; Col.Title 3   ; Col.Title 4 

Sheet Title1;       RowTitle1                  ; Value               ; Value            ; Value            ; Value

Sheet Title1;       RowTitle2                  ; Value               ; Value            ; Value            ; Value

(at which point I could then unpivot the whole thing)

Thanks!

Alex

[DaFloDo · Accepted solution]

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]

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]

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....