Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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
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
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.
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....
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
84 | |
66 |