Forum Discussion
Generate a new table with different values per ID
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 groupRowsThe '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
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
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
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