cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alex Regular Visitor
Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
DaFloDo Regular Visitor
Regular Visitor

Re: Move Sheet Titles to Rows ( Power Query )

hi @alex,

 

this is how I achieved the result ...

title to rows.png

 

 

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

3 REPLIES 3
cmaz Regular Visitor
Regular Visitor

Re: Move Sheet Titles to Rows

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 Regular Visitor
Regular Visitor

Re: Move Sheet Titles to Rows

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

Highlighted
DaFloDo Regular Visitor
Regular Visitor

Re: Move Sheet Titles to Rows ( Power Query )

hi @alex,

 

this is how I achieved the result ...

title to rows.png

 

 

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