Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to merge colums based on duplicate block numbers so I would like all the information on 2 (or more) seperate lines to merge together for all the rows that have the same block number ('Block #' Column).
The 'Accept/Reject' column will always be 'Major' for this table. For the 'Date Raised' column I would like the earliest date. All the other columns I would like contatenated together with commas seperating them.
I would greatly appreciate any help!
Below I have shown an example of the data set I have and what I would like it to look like:
Solved! Go to Solution.
Hi @Elliot137 ,
The following example query turns this:
...into this:
Example Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNvFR0lEyNDU0VYrViVYyMTYyDAcJmFgagAWQVJhDBSwNXMEqLEzQBMyNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Block #" = _t, #"M/C" = _t]),
groupRows = Table.Group(Source, {"Block #"}, {{"M/C", each Text.Combine([#"M/C"], ", "), type nullable text}})
in
groupRows
Summary:
Group By whichever columns you want to keep.
Create aggregate columns for each of the columns you want to combine using the SUM (or any statistical) operator.
Edit the resulting code in the formula bar to change List.Sum(...) to Text.Combine(..., ", ") for each of the columns to be combined.
So this:
...goes to this:
For your date column, you can just choose the MIN operator during the Group By process.
Pete
Proud to be a Datanaut!
Hi @Elliot137 ,
The following example query turns this:
...into this:
Example Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNvFR0lEyNDU0VYrViVYyMTYyDAcJmFgagAWQVJhDBSwNXMEqLEzQBMyNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Block #" = _t, #"M/C" = _t]),
groupRows = Table.Group(Source, {"Block #"}, {{"M/C", each Text.Combine([#"M/C"], ", "), type nullable text}})
in
groupRows
Summary:
Group By whichever columns you want to keep.
Create aggregate columns for each of the columns you want to combine using the SUM (or any statistical) operator.
Edit the resulting code in the formula bar to change List.Sum(...) to Text.Combine(..., ", ") for each of the columns to be combined.
So this:
...goes to this:
For your date column, you can just choose the MIN operator during the Group By process.
Pete
Proud to be a Datanaut!