Forum Discussion

IEPMost's avatar
IEPMost
Helper III
5 years ago

Group multiple columns except one

Hi guys,   is it possible to group two columns into a list and keep one as it is in one step (in Power Query)?   i.e.: I have three columns [Date], [Sales], [CustomerID]; in the next step I want ...
Need Help
camargos88's avatar
camargos88
Community Champion
5 years ago

Hi IEPMost ,

 

It's possible having a list of tables with those 2 columns. However you can't use it on your visuals.

Can you explain more about what you are trying to achieve ?

  • IEPMost's avatar
    IEPMost
    Helper III
    5 years ago

    Hi camargos88 ,

     

    thanks for your quick response!!

     

    I want to transform two of the columns ([CustomerID] and [Sales]) into a third column, which should display a list. In the same step I want to keep the column [Date].

     

    This is my Code (you can ignore the if condition):

    = Table.Group(#"Changed Type", {"CustomerID", "Sales"}, {{"ERROR", each List.Transform(List.Numbers(1,Table.RowCount(_)), each if _ = 1 then "GO" else "Error"), type list}})

     

    So how can I keep the column [Date] without grouping it with [CustomerID] and [Sales]?

     

    Thanks a lot!!:)

    • camargos88's avatar
      camargos88
      Community Champion
      5 years ago

      IEPMost ,

       

      Can you provide an example of the input and desired output ?

       

      Thanks

      • IEPMost's avatar
        IEPMost
        Helper III
        5 years ago

        camargos88

         

        yes sure!!

         

        So here are more details: I have three columns [Date], [CustomerID] and [meter reader]; if the meter reader stops counting for a certain CustomerID, a further column should display "Error" otherwise "GO". Therefore I have this formula:

         

        #"Grouped Rows" = Table.Group(#"Changed Type", {"Reglernummer", "Wert"}, {{"ERROR", each List.Transform(List.Numbers(1,Table.RowCount(_)), each if _ = 1 then "GO" else "Error"), type list}}),

         

        #"Expanded ERROR" = Table.ExpandListColumn(#"Grouped Rows", "ERROR")

         

        After this step the column [Date] disappears (I know why, but I don´t know how to fix it) and I have to know when (on which day) the meter reader stops counting. So here is how it looks before:

         

        Date CustomerIDmeter reader
        01.01.2020 31000
        02.01.2020 31500
        03.01.2020 31500
        04.01.2020 31500
        01.01.2020 5200
        02.01.2020 5300
        03.01.2020 5400
        01.01.2020 81000
        02.01.2020 81100
        03.01.2020 8

        1100

         

        and this should be my output (in my report it´s the same just without the date):

         

        Date CustomerIDmeter readerStatus
        01.01.2020 31000GO
        02.01.2020 31500GO
        03.01.2020 31500ERROR
        04.01.2020 31500ERROR
        01.01.2020 5200GO
        02.01.2020 5300GO
        03.01.2020 5400GO
        01.01.2020 81000GO
        02.01.2020 81100GO
        03.01.2020 8

        1100

        ERROR

         

         

         

        So how can I use my formula for showing me the meter reader, which stopped counting and at the same time keep my Date [Column]? I hope you can help me out!!


        Thank you:)