Forum Discussion
Helper IIAssign custom labels to unpivoted data
My original data looks like below.
After unpivoting as shown below Column A, B and C.
I want to add a new column D in PQ and assing labels shown in column D based on row numbers e.g row 2 to 11 as Cat 1; row 12 to 200 as Cat 2...till row 375 as Cat 5.
The screenshot below does not show all 375 columns. I simplified to fit all five categories within 12 row values and repeated it.
Here you go PowerBI_Query
Here is the code:let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Heading 1"}, "Attribute", "Value"), RecordCount = Table.RowCount(#"Unpivoted Other Columns"), CategoryList = List.Repeat(CategoryLabels, RecordCount/373), TableWithCategory = Table.ToColumns(#"Unpivoted Other Columns") & {CategoryList}, #"Final Table" = Table.FromColumns(TableWithCategory, Table.ColumnNames(#"Unpivoted Other Columns") & {"Category"}) in #"Final Table"For clarity I got rid of the Changed Type step as it had 300+ change types. If you can get away with that, do that after the unpivot, but not the end of the world if done before.
Here is what I did:- Got the number of records
- Repeated the list of category labels in a separate list by the number of records divided by 373. If that is not evenly divisible by 373 you will need to add additional logic to truncate the last time it happens.
- Converted the original table to a list of columns, then appended the category list.
- Converted that list of columns back to a table and got the column names and reapplied them.
8 Replies
- edhans
Community Champion
Your picture doesn't match your description. You said assign CAT1 to rows 2-11, but only rows 2 & 3 have CAT1, as well as rows 14 & 15, and 26 & 27.
Also, I cannot paste an image into Power Query. Please give us data per below. But more importantly, explain in clear detail what you want, as the above is contradictory.How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
PowerBI_QueryHere is the workbook link for pratice
Like I said The screenshot below does not show all 375 columns. I simplified to fit all five categories within 12 row values and repeated it.
You can try the solution on sample file later I will scale it with orginal data.
Let me know if it is still not clear.
- edhans
Sorry. I don't understand the logic. If I don't understand why rows 2-3 are CAT1, and 4-6 are CAT2, etc, I cannot write a formula that works other than hardcoding.
If you cannot explain it to me in English, I cannot explain it to you in M code. 😁
You can add an index column using the toolbar, starting at 1, and then just do if ranges, like
Table.AddColumn(PriorStepOrTableName, "Category", each if [Index] > 300 then "Cat5" else if [Index] > 250 then "Cat4" else if [Index] > 12 "Cat3" else if [Index] > 2 then "Cat2" else "Cat1", type text)
--Nate
- PowerBI_Query
The labels have to cycle every 373 rows
- edhans
Here you go PowerBI_Query
Here is the code:let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Heading 1"}, "Attribute", "Value"), RecordCount = Table.RowCount(#"Unpivoted Other Columns"), CategoryList = List.Repeat(CategoryLabels, RecordCount/373), TableWithCategory = Table.ToColumns(#"Unpivoted Other Columns") & {CategoryList}, #"Final Table" = Table.FromColumns(TableWithCategory, Table.ColumnNames(#"Unpivoted Other Columns") & {"Category"}) in #"Final Table"For clarity I got rid of the Changed Type step as it had 300+ change types. If you can get away with that, do that after the unpivot, but not the end of the world if done before.
Here is what I did:- Got the number of records
- Repeated the list of category labels in a separate list by the number of records divided by 373. If that is not evenly divisible by 373 you will need to add additional logic to truncate the last time it happens.
- Converted the original table to a list of columns, then appended the category list.
- Converted that list of columns back to a table and got the column names and reapplied them.
Oh understood-- in that case, just use Table.Split(PriorStepOrTableName, 377). This gives you a list of tables, each 377 rows. Turn it to a table (it's probably in list format, make it a table column, but don't expand the tables). Then you can add an index starting at 1 to each of the grouped tables, like
Table.AddColumn(PriorStepOrTableName, "NewTable", each Table.AddIndexColumn([NameOfTableColumn, "Index", 1))
Now you can expand the tables and apply my original answer.
--Nate
