Forum Discussion

TheHans's avatar
TheHans
Helper I
4 years ago
Solved

Generate a new table with different values per ID

Dear Community,

 

I have a table with different report dates containing different values for the same ID. 
For IssueID 1, the due date has changed over time. I would like to create a new table with the columns "IssueID", "AmountDiffDueDates" and "DueDates" which contains all different due dates comma separated.

Is there a way how I can do that in PoweQuery?

Thanks a lot in advance.

 

ReportDateIssueIDIssueNameDueDate
20.02.20221A15.03.2022
20.02.20222B10.03.2022
20.02.20223C11.03.2022
15.02.20221A03.03.2022
15.02.20222B10.03.2022
15.02.20223C11.03.2022
10.02.20221A25.02.2022
10.02.20222B10.03.2022
10.02.20223C11.03.2022
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi TheHans ,

     

    Paste this over the default code in a new blank query:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLQMzDSMzIwMlLSUTIEYkcQbapnYAwRjNVBUwPCTiA1BrjVGAOxM0iNIYoakLEYdgEV4FSDwy4UNbjswuYvI4RGDDW47CLkr1gA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ReportDate = _t, IssueID = _t, IssueName = _t, DueDate = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"DueDate", type date}}),
    remOthCols = Table.SelectColumns(chgTypes,{"IssueID", "DueDate"}),
    chgDueDateToText = Table.TransformColumnTypes(remOthCols,{{"DueDate", type text}}),
    groupRows =
        Table.Group(
            chgDueDateToText,
            {"IssueID"},
            {
                {"noofDiffDueDates", each Table.RowCount(Table.Distinct(_)), Int64.Type},
                {"dueDates", each Text.Combine(List.Distinct([DueDate]), ", "), type text}
            }
        )
in
    groupRows

     

     

    The 'groupRows' step is a customised Group By function, so you may need to add this as a custom step into your query.

     

    This gives the following output:

     

    Pete

5 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi TheHans ,

     

    Paste this over the default code in a new blank query:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLQMzDSMzIwMlLSUTIEYkcQbapnYAwRjNVBUwPCTiA1BrjVGAOxM0iNIYoakLEYdgEV4FSDwy4UNbjswuYvI4RGDDW47CLkr1gA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ReportDate = _t, IssueID = _t, IssueName = _t, DueDate = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"DueDate", type date}}),
    remOthCols = Table.SelectColumns(chgTypes,{"IssueID", "DueDate"}),
    chgDueDateToText = Table.TransformColumnTypes(remOthCols,{{"DueDate", type text}}),
    groupRows =
        Table.Group(
            chgDueDateToText,
            {"IssueID"},
            {
                {"noofDiffDueDates", each Table.RowCount(Table.Distinct(_)), Int64.Type},
                {"dueDates", each Text.Combine(List.Distinct([DueDate]), ", "), type text}
            }
        )
in
    groupRows

     

     

    The 'groupRows' step is a customised Group By function, so you may need to add this as a custom step into your query.

     

    This gives the following output:

     

    Pete

    • TheHans's avatar
      TheHans
      Helper I
      4 years ago

      Wow!! This is awesome! Thanks a lot. It was really easy to use your code. How did you create that source? I never saw that before. 

      Do you use the advanced editor to write that custom "groupRows" code?

      Cheers

      Hans

      • BA_Pete's avatar
        BA_Pete
        Super User
        4 years ago

        TheHans ,

         

        The source was created by pasting your example table into 'Enter Data' in Power Query. PQ represents this as a JSON Binary in text format.

         

        To create the custom step, I first used Group By from the GUI, but added the second column as SUM of [DueDate]. This outputs an error, but very quickly sets up the code structure needed. I then used Advanced Editor to adjust the code for the second column to include functions (List.Distinct, Text.Combine) that aren't available via the GUI.

         

        Pete