Assign custom labels to unpivoted data

[PowerBI_Query]

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.

[watkinnc]

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

[watkinnc]

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 · Accepted 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:

1. Got the number of records
2. 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.
3. Converted the original table to a list of columns, then appended the category list.
4. Converted that list of columns back to a table and got the column names and reapplied them.

Here is the file back.

[edhans]

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_Query]

Here 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. 😁

[PowerBI_Query]

Okay, 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.