Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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....
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |