I will do all transformations in the same workbook and that’s why I use a named range “ ChargesExt” to refer to a data on the sheet.īut if you don’t want to add named ranges, you can make a query from another workbook to an Excel file (if you make a query from Power BI, it is almost only way).
This report differs from the same in previous post with not only extended header, but also Jane Doe was replaced to John Doe from Canada to make duplicates in the original data. To make a story from this case, let it be a monthly members’ charges report for the mysterious “JohnDoes International Club”. The sample workbook for this case can be downloaded here. And yes, we can do it in Power Query without one hand-made “M” language code line – just with UI.
These rows form the unique header, but at the same time, individual values in them are not unique and may be repeated in the headers of other tables.Īs usual, there are more than only one solution of this task: we can use VBA, we can use Excel formulas, but we want to use Power Query (“Get & Transform” in Excel 2016 or query editor in Power BI Desktop). Each table is preceded by a header of several rows. You can see that the report that we need to convert, composed of several tables with different number of rows (this prevents us from using a trick with a column index for grouping rows). What we have to do: attach title fields to data rows.
0 kommentar(er)
