CODEWITHSUNDEEP
powerbipowerquery
Ignacio Toro
Ignacio Toro

Reputation: 3

Transform data in power query grouping data

I have the following table in power query:

enter image description here

I want to group by animals in two columns, one accepted and another rejected, separated by commas:

enter image description here

I tried concatenating the columns animals - status and then using the function group by, obtaining the following result:

enter image description here

The power query code that tried:

let
Origen = Excel.Workbook(File.Contents("C:\Users\user\Desktop\example_animals_status.xlsx"), null, true),
Example_Sheet = Origen{[Item="Example",Kind="Sheet"]}[Data],
#"Encabezados promovidos" = Table.PromoteHeaders(Example_Sheet, [PromoteAllScalars=true]),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos",{{"code number", Int64.Type}, {"animal", type text}, {"status", type text}}),
#"Columnas combinadas" = Table.CombineColumns(#"Tipo cambiado",{"animal", "status"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"combined"),
#"Tipo cambiado1" = Table.TransformColumnTypes(#"Columnas combinadas",{{"code number", type text}}),
#"Filas agrupadas" = Table.Group(#"Tipo cambiado1", {"code number"}, {{"Recuento", each Text.Combine([combined],";"), type text}}) in #"Filas agrupadas"

But I don't know how to split and create the two columns with the rejected and accepted animals. Thank you in advance

Upvotes: 0

Views: 175

Answers (1)

Olly
Olly

Reputation: 7891

First off, this is Power Query Language (M), not DAX.

Now, you can group by code and status, and combine the distinct animals for each combination;

    #"Grouped Rows" = Table.Group(#"Tipo cambiado", {"code number", "status"}, {{"animals", each Text.Combine(List.Sort(List.Distinct(_[animal]),Order.Ascending),", "), type text}}),

Then you can pivot the table on status;

    #"Pivoted Status" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[status]), "status", "animals")

So your new query becomes:

let
    Origen = Excel.Workbook(File.Contents("C:\Users\user\Desktop\example_animals_status.xlsx"), null, true),
    Example_Sheet = Origen{[Item="Example",Kind="Sheet"]}[Data],
    #"Encabezados promovidos" = Table.PromoteHeaders(Example_Sheet, [PromoteAllScalars=true]),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos",{{"code number", Int64.Type}, {"animal", type text}, {"status", type text}}),
    #"Grouped Rows" = Table.Group(#"Tipo cambiado", {"code number", "status"}, {{"animals", each Text.Combine(List.Sort(List.Distinct(_[animal]),Order.Ascending),", "), type text}}),
    #"Pivoted Status" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[status]), "status", "animals")
in
    #"Pivoted Status"

Output looks like this:

enter image description here

Upvotes: 0

Related Questions