![]() * Counter used by List.TransformMany to iterate over the lists (row data) in the list. ![]() Each row is a full list with the number of items in the list being the original number of columns*/ ConvertedToRows = Table. * Convert the header rows to a list of lists. Let DelimiterToUse = if Delimiter = null then " " else Delimiter, ( OriginalTable as table, HeaderRows as number, optional Delimiter as text) => Once you click OK, your data in Power Query window should look something like this: Make sure you uncheck “My table has headers” because it will only make the top row the header, and we don’t want that. If you have an older version of Excel it may say From Table or Range. If you are using Excel, just put your cursor on the data somewhere and then the Data ribbon, select “From Sheet” if you are using Office 365. The Manual Method Using the Power Query Interface Remember - never pre-transform your data before you transform it in Power Query. You’ll just have to do it manually again the next time you get a file from someone. ![]() We do not want to consolidate the headers in Excel. For simplicity’s sake, I’ll use Power Query in Excel for this since my data is in Excel already, but the same logic would hold if you were importing the data into Power Bi. The second way will be a custom function that will do all of the work for you. The first way will be to do it manually mostly using the Power Query user interface. Getting this info into Power Query can sometimes be a challenge.
0 Comments
Leave a Reply. |