Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingOh 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
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
The labels have to cycle every 373 rows
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:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYour 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHere 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.
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. 😁
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingOkay, I prepared a test file. Workbook link
The reason I don't use first row as header because excel has characters limit for headings PQ doesn't. So I promote header in PQ editor. My headings are very long.
Each unique number has 373 rows of data.
I labelled the data for all 374 rows with Cat 1 to Cat 7. The labels cycle every 373 rows for each unique number. E.g Unique 1 ends at row 374 (373 + heading row) with last label being Cat 7. Unique 2 starts at row 375 with Cat 1 and ends at 747 with Cat 7…etc
You can navigate through the ranges by copying the row reference given in column H.
There will be new addition of columns next week so I need to manually edit or tweak M code once you hard code the range for each Cat.
In the test file I had only two Unique numbers in original data I have about 11000 unique numbers. If I unpivot all of it, there will be nearly half a million rows and then I have to merge it with other tables it get even larger.