Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
I posted another thread about this topic and got lots of solutions that all worked, but the reality of what we need our data to look like keeps changing based on the client's feedback. I've created a new thread but it was getting progressively confusing as our client's desires kept changing.
In my data, we have unique IDs (the A and Bs) that have multiple rows of data (ranging from numbers to dates) that need to be combined into one row for each unique ID. For some columns, only one row has data. In these cases, we want the row with the data to be kept. However, in other columns, multiple rows have data. For these columns, the client wants the row with the greatest number or most recent date to be to be kept.
Here is an example of what our data currently looks like:
Here is what we want our data to look like:
Solved! Go to Solution.
Hi @GraceJinM,
Bit confused here, based on your example you are taking the max value from each column, not "selecting a row" based on a max value. Well, give this a go.
let
Source = Table.FromRows(
{
{"A", 1, 2, #date(2024, 9, 3)},
{"A", null, 4, #date(2024, 9, 4)},
{"B", 4, #date(2024, 9, 2), #date(2024, 9, 3)},
{"B", 3, null, #date(2024, 9, 5)}
},
{"Section N", "Column1", "Column2", "Column3"}
),
GroupRows = Table.Group(Source, {"Section N"},
{{"t", each [Column1=List.Max([Column1]), Column2=List.Max([Column2]), Column3=List.Max([Column3])]}}
),
Expand = Table.ExpandRecordColumn(GroupRows, "t", {"Column1", "Column2", "Column3"})
in
Expand
I hope this is helpful
Hi @GraceJinM,
Bit confused here, based on your example you are taking the max value from each column, not "selecting a row" based on a max value. Well, give this a go.
let
Source = Table.FromRows(
{
{"A", 1, 2, #date(2024, 9, 3)},
{"A", null, 4, #date(2024, 9, 4)},
{"B", 4, #date(2024, 9, 2), #date(2024, 9, 3)},
{"B", 3, null, #date(2024, 9, 5)}
},
{"Section N", "Column1", "Column2", "Column3"}
),
GroupRows = Table.Group(Source, {"Section N"},
{{"t", each [Column1=List.Max([Column1]), Column2=List.Max([Column2]), Column3=List.Max([Column3])]}}
),
Expand = Table.ExpandRecordColumn(GroupRows, "t", {"Column1", "Column2", "Column3"})
in
Expand
I hope this is helpful
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
11 | |
8 | |
8 | |
7 |
User | Count |
---|---|
14 | |
13 | |
9 | |
7 | |
6 |