Separate duplicate values with order number by columns

[thiendt5]

Hello all!
I'm new to Power BI and I'm creating Error code tracking in Power BI.
Please help to see in below example:

I have the table below. Each IMEI is tested many times and generate many diffirent error codes:

Is there any DAX function formula could create new columns like this? I just want to show all error code of a IMEI by rows

I tried IF function but it didn't work
ERROR_1st = IF([Test_time]=1,[Error_code])
ERROR_2nd = IF([Test_time]=2,[Error_code])

The result i've got as below and I can't see all error code at a time when filtering:

Please suggest for this case. Thanks!

[v-alq-msft · Accepted solution]

Hi, @thiendt5 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a calculated column as below.

ERROR_Num = 
SWITCH(
    'Table'[Test_time],
    1,"ERROR_1st",
    2,"ERROR_2nd",
    3,"ERROR_3nd", 
    4,"ERROR_4th",
    5,"ERROR_5th"
)

Then you may use 'Matrix' visual to display the result.

Or you may create a calculated table with following dax.

Result Table = 
ADDCOLUMNS(
    DISTINCT('Table'[IMEI]),
    "ERROR_1st",
    CONCATENATEX(
        FILTER(
            'Table',
            [IMEI]=EARLIER('Table'[IMEI])&&
            [Test_time]=1
        ),
        [Error_code],
        " "
    ),
    "ERROR_2nd",
    CONCATENATEX(
        FILTER(
            'Table',
            [IMEI]=EARLIER('Table'[IMEI])&&
            [Test_time]=2
        ),
        [Error_code],
        " "
    ),
    "ERROR_3rd",
    CONCATENATEX(
        FILTER(
            'Table',
            [IMEI]=EARLIER('Table'[IMEI])&&
            [Test_time]=3
        ),
        [Error_code],
        " "
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@thiendt5 So maybe like the following:

Column = "ERROR_" & [Test_time]

You could then use that in a matrix visualization as the Column field. 

[amitchandak]

@thiendt5 , You have pivot in power query. Otherwise, you have to create new column.

https://radacad.com/pivot-and-unpivot-with-power-bi

The option you want to try need filter and grouping. If pivot do not work

[thiendt5]

@amitchandak I'm sorry but pivot do not work. I created [Test_time] column by DAX so it can not be shown in Query Editor.

Could you suggest filter and grouping DAX function for this case?

[amitchandak]

@thiendt5 , you can create a new table using summarize 

like

summarize(Table, Table[itei] , ",ERROR_1st" ,maxX(Table, IF([Test_time]=1,[Error_code])) , "ERROR_2nd",maxX(Table, IF([Test_time]=2,[Error_code])))

Add other columns 

Or this how you can have new column in M/edit query

ERROR_1st = if [Test_time]=1 then [Error_code]

Then you can use aggregate column