Generate a new table with different values per ID

[TheHans]

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.

ReportDate	IssueID	IssueName	DueDate
20.02.2022	1	A	15.03.2022
20.02.2022	2	B	10.03.2022
20.02.2022	3	C	11.03.2022
15.02.2022	1	A	03.03.2022
15.02.2022	2	B	10.03.2022
15.02.2022	3	C	11.03.2022
10.02.2022	1	A	25.02.2022
10.02.2022	2	B	10.03.2022
10.02.2022	3	C	11.03.2022

[BA_Pete · Accepted solution]

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]

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]

@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

[BA_Pete]

FYI, I've just noticed that I didn't specify the output data type on the second column.

I've update the code slightly so it outputs as text type, so you don't need to do another Changed Types step afterwards.

Pete

[TheHans]

Even better. Thanks.