Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear Team,
Kindly Help me For Removing Duplicate Data.
Date | Sale | Art |
01-04-2018 | 100 | A |
02-04-2018 | 200 | B |
03-04-2018 | 300 | C |
04-04-2018 | 400 | D |
05-04-2018 | 500 | E |
06-04-2018 | 600 | F |
01-04-2018 | 400 | A |
In Report Making 01-04-2018, It Show Only 400 Sale means Updated Records..How to Show
Kindly Help me
I read Many Thread And i Saw Below Steps But i am not understanding how to apply thses Steps
1. Use CONTAINS function to get the records which both contain the two keys.(duplicate records)
2. Filter inactive records from above records.(inactive records of duplicate records)
3. Use EXCEPT function to exclude the inactive records from original records, then you will get the active records and unique records which you want.
Solved! Go to Solution.
In the query editor, you add an index-column:
Check the new index-column and sort descending:
Edit the formula in the formula bar to this (otherwise the sort-order will not be kept):
Check columns "Date" and "Art", and remove duplicates:
This is the code that you can paste into the advanced editor to follow the steps along:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc6xDcAgDETRXVyDZINBtCQhSyD2XyPWWXKR5opXnP7exJJZswxKJMy2k04yLsEFfDnX4Aq+nTVYwY9zC27g5dyDO/h1lt+JlZwP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Sale = _t, Art = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sale", Int64.Type}, {"Art", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Sorted Rows" = Table.Buffer(Table.Sort(#"Added Index",{{"Index", Order.Descending}})), #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Date", "Art"}) in #"Removed Duplicates"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
If your aim is to have 500 as Output you just need to SUM(Table[Sales]).
As long as you don't need any extraordinaries this should do the job.
If not please give as further information how your result should look like.
Best regards.
Kindly Tell me
Dear Xiaoxin Sheng,
How to apply these three steps...Kindly tell me
In the query editor, you add an index-column:
Check the new index-column and sort descending:
Edit the formula in the formula bar to this (otherwise the sort-order will not be kept):
Check columns "Date" and "Art", and remove duplicates:
This is the code that you can paste into the advanced editor to follow the steps along:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc6xDcAgDETRXVyDZINBtCQhSyD2XyPWWXKR5opXnP7exJJZswxKJMy2k04yLsEFfDnX4Aq+nTVYwY9zC27g5dyDO/h1lt+JlZwP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Sale = _t, Art = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sale", Int64.Type}, {"Art", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Sorted Rows" = Table.Buffer(Table.Sort(#"Added Index",{{"Index", Order.Descending}})), #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Date", "Art"}) in #"Removed Duplicates"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi,
When i load Next 4-Feb-2018 Value
Date Sale Art
4-Feb-2018 900 B
After Refresh it should be 900 Sale means update record..
But it is showing 200..you Sent me it is working only first row..But i want to update any row..Kindly Help Me
Sorry It's Working.Thanks
Hi,
How can i make a composite key in power Query editaor Means Date&'-'&Art 01-04-2016&'-'&A
How can i make..Kindly help me
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |