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.
Hi All,
I'd like to remove duplicates in my dataset based on a logical primary key (ID) and retain the latest modified records values.
Solved! Go to Solution.
You can achieve this using power query, click query editor and copy the original table. Then click "Transform"-> "Group By" as below:
Then merge the original table with duplicated tabel as below:
The result is as below, you can also refer to the pbix file.
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You can achieve this using power query, click query editor and copy the original table. Then click "Transform"-> "Group By" as below:
Then merge the original table with duplicated tabel as below:
The result is as below, you can also refer to the pbix file.
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-yuta-msft, @parhamasq, This method will not return the most recent value, but instead the largest, which would give incorrect results if value1 decreases. If 62 is replaced with 42 (for example) in the source table, the final table does not show the most recent update for bill, but instead the largest.
Source:
end result:
Hi @parhamasq
try to use a new calculated table like this
NewTable =
ADDCOLUMNS(
SUMMARIZE(
'Table';
'Table'[ID];'Table'[Title];"Last Modified new";MAX('Table'[Last Modified])
);
"Value";calculate(max('Table'[Value1]);filter('Table';'Table'[Last Modified]=[Last Modified new] && 'Table'[ID]=[ID])))
do not hesitate to give a kudo to useful posts and mark solutions as solution
This is an error:
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
hi @parhamasq
do you have rows which has a different ID and Title values in your data model?
do not hesitate to give a kudo to useful posts and mark solutions as solution
Thank you
I'm a new joiner in Power BI,
Would you please explain to me more about create the table in Power BI?
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.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |