Forum Discussion

yoav20007's avatar
yoav20007
Helper II
6 years ago
Solved

Can i do it in PQ?

  • Hi. Sure you can. You can do almost everything with Power Query. Let me copy the code and if you have doubts you can ask for a pbix with the example and the same code. I tried to use your column names for this. Think as Source the word "Origen". I'm sorry I hace my Power Bi in spannish. 

        #"Filas agrupadas" = Table.Group(Origen, {"Name"}, {{"ToTable", each _, type table [Columna1=text, Columna2=text]}}),
        #"Personalizada agregada" = Table.AddColumn(#"Filas agrupadas", "ToList", each Table.ToList(Table.SelectColumns([ToTable], "Product"))),
        #"Personalizada agregada1" = Table.AddColumn(#"Personalizada agregada", "ByCommas", each Text.Combine([ToList], ",")),
        #"Dividir columna por delimitador" = Table.SplitColumn(#"Personalizada agregada1", "ByCommas", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Product.1", "Product.2", "Product.3", "Product.4"}),
        #"Columnas quitadas" = Table.RemoveColumns(#"Dividir columna por delimitador",{"ToTable", "ToList"})
    

    You have to group by, then convert that to list, join together by commas and finally split commas in columns.

    Hope this help

     

    Regards,

2 Replies

  • Hi. Sure you can. You can do almost everything with Power Query. Let me copy the code and if you have doubts you can ask for a pbix with the example and the same code. I tried to use your column names for this. Think as Source the word "Origen". I'm sorry I hace my Power Bi in spannish. 

        #"Filas agrupadas" = Table.Group(Origen, {"Name"}, {{"ToTable", each _, type table [Columna1=text, Columna2=text]}}),
        #"Personalizada agregada" = Table.AddColumn(#"Filas agrupadas", "ToList", each Table.ToList(Table.SelectColumns([ToTable], "Product"))),
        #"Personalizada agregada1" = Table.AddColumn(#"Personalizada agregada", "ByCommas", each Text.Combine([ToList], ",")),
        #"Dividir columna por delimitador" = Table.SplitColumn(#"Personalizada agregada1", "ByCommas", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Product.1", "Product.2", "Product.3", "Product.4"}),
        #"Columnas quitadas" = Table.RemoveColumns(#"Dividir columna por delimitador",{"ToTable", "ToList"})
    

    You have to group by, then convert that to list, join together by commas and finally split commas in columns.

    Hope this help

     

    Regards,