Forum Discussion
Remove duplicates based on conditions in Power Query
Hi all,
I have a folder where the new raw data will be posted - however each file will overwrite a part of the data from the previous one and I want to apply the remove duplicates to one column, however here I have some specifics about this column. It looks like this:
143456921
129804921
164842312
0
0
123842756
0
123843563
634632921
433842921
0
So I want:
to KEEP all the ZEROS and to apply Remove Duplicates only to the values which are different than 0 (the rest)
Is this possible?
Many thanks!
Best regards,
Ivan
hi IPGeorgiev
It has the same logic, and you could also nested them in one step.
For example:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZFLDoMwDAWvUmXNAmKTwlkQS1MioSBBCu3tcT6QoHbp8YvtUbpOVAhYq1ZWohCzIdEXzGTblBiY3efAFDYooZKOjQuFZMnVML+XVOjtnAGcf9aK4ao/V2CljUxKQK2AKenXaD1VgApk2G10PAjBDYsHxeduGE1pWuZhd5q2PyqjXiyd+czHGVyNYDHcahZID3Mxt/6W9CL3bFR0Mlcjt+Rrjf3+enr8OP+kTMh/SX8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column = _t, Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", Int64.Type}}),
#"a"=Table.Combine( {Table.Distinct(Table.SelectRows(#"Changed Type", each [Column] <> 0), {"Column"}),Table.SelectRows(#"Changed Type", each [Column] = 0)})
in
#"a"here is sample pbix file, please try it.
Regards,
Lin
3 Replies
ImkeF probably has a better way to do this. See attached PBIX. I created 2 reference queries. In one I filtered to just the zeros. In the other I filtered out the zeros and then removed duplicates. Then I appended the two queries together.
Thanks a lot for your time and for the suggestion!
However I dont see how to use this for my case as I forgot to say that this is just one column where I need to remove the duplicates, but he raw data it self has many other columns so I need to find a way to this in the same table I think. (I am rather new to Power BI)
hi IPGeorgiev
It has the same logic, and you could also nested them in one step.
For example:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZFLDoMwDAWvUmXNAmKTwlkQS1MioSBBCu3tcT6QoHbp8YvtUbpOVAhYq1ZWohCzIdEXzGTblBiY3efAFDYooZKOjQuFZMnVML+XVOjtnAGcf9aK4ao/V2CljUxKQK2AKenXaD1VgApk2G10PAjBDYsHxeduGE1pWuZhd5q2PyqjXiyd+czHGVyNYDHcahZID3Mxt/6W9CL3bFR0Mlcjt+Rrjf3+enr8OP+kTMh/SX8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column = _t, Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", Int64.Type}}),
#"a"=Table.Combine( {Table.Distinct(Table.SelectRows(#"Changed Type", each [Column] <> 0), {"Column"}),Table.SelectRows(#"Changed Type", each [Column] = 0)})
in
#"a"here is sample pbix file, please try it.
Regards,
Lin
