Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.