Merging multiple rows based on condition in Power BI

subhro 0
Viewed 41

I have a table that looks like the one below.

Table I have

enter image description here

I want an output classifying the PART as either CLASS A or B. CLASS A and CLASS B should be created as two additional columns.

The conditions for classifications are:

-> IF Arrangement, Prefix and Range are the same, it will be classified as CLASS A and CLASS B, and the PART should be in both columns.

-> If the above condition is false, it will check the value in the TYPE column. If the type column contains CLASS A, then the respective PART should be in the CLASS A column. If the type column contains CLASS B, then the separate PART should be in the CLASS B column.

Here is the sample of the output I would need.

Table I want

enter image description here

So far, I have managed to get the below output but have not been able to merge the results into one row when the three columns are the same.

Results I got till now

Results I got till now

Sample Data

Arrangements    PREFIX  RANGE   PART    TYPE
ARR1    1XJ 1-100   191123  TRANSMISSION CLASS A
ARR1    1XJ 1-100   299123  TRANSMISSION CLASS B
ARR1    9TC 1-100   191123  TRANSMISSION CLASS A
ARR2    5TJ 101-120 288123  TRANSMISSION CLASS B
merge dax powerbi
2 Answers

Try this in M Advanced Editor

Table.AddColumn(previousStepName, "Text After Delimiter", each Text.AfterDelimiter([TYPE], " "), type text)
xlmaster0

In the Query Editor (Transform Data), you can

  • Group by Arrangements, Prefix and Range and use the ALL operation

  • Then add Custom Columns with the formula below for CLASS A

    =try Table.SelectRows([Count], each Text.Contains([TYPE],"CLASS A"))[PART]{0} otherwise null

enter image description here

  • Add another Custom Column for CLASS B with the obvious changes
  • Delete the Count column

enter image description here

Ron Rosenfeld0
Related