Power BI: grouping items in one colum, other columns that have different values appear as several columns

Question

I have a question for a table on Power BI. In fact, I have a first colums that is the name or the id of a process, the 2nd one, that is the number of the step of the process, and the 3rd one that is the time of the step. Example :

Column 1: [A, A, B, A, C, B, C]

Column 2: [1, 2, 1, 3, 1, 2, 2]

Column 3: [8, 9, 6, 10, 18, 7, 19]

I want it to appear as a table with the first colum the process (without duplicate), and new columns with the steps and associated hours, like :

Column 1: [A, B, C]

Column 2: [8, 6, 18] #hour of the step 1 of each process

Column 3: [9, 7, 19] #hour of the step 2 of each process

Column 4: [10, Nan, Nan] #hour of the step 3 of each process

Is this possible to do directly on Power BI or I need to pass by other tools such as python? Thank you,

Answer 1

You can achieve this with following measures

_1 = MINX(FILTER(tbl,tbl[colB]=1),tbl[colC])

_2 = MINX(FILTER(tbl,tbl[colB]=2),tbl[colC])

_3 = MINX(FILTER(tbl,tbl[colB]=3),tbl[colC])

A power query solution would be following

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYgulWB0IzwiILeE8Y5C8AZjrBFVqBueBlJqDec5QOUMLOBckaQg0KBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [colA = _t, colB = _t, colC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"colA", type text}, {"colB", Int64.Type}, {"colC", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"colB", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"colB", type text}}, "en-US")[colB]), "colB", "colC", List.Min)
in
    #"Pivoted Column"