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 to everyone,
First of all thank you for helping other and hopefully me to learn how to use succesfully Power BI.
Hopefully i'm not crossposting..
I need to filter a table in order to keep the registers with non unique values.
Example:
batchIdentifier | startDate | workplace |
Part 1 | 01/01/2021 14:30 | wp 1 |
Part 2 | 01/01/2021 14:46 | wp 1 |
Part 3 | 01/01/2021 14:51 | wp 1 |
Part 3 | 01/01/2021 15:15 | wp 2 |
Part 2 | 01/01/2021 15:32 | wp 2 |
Part 4 | 01/01/2021 17:31 | wp 1 |
Part 1 | 01/01/2021 17:51 | wp 2 |
Part 3 | 01/01/2021 18:51 | wp 3 |
In other to exclude the unique register.
If someone knows how to solve that i would be awesome 😄
Thanks in advance,
Joan
Solved! Go to Solution.
Hi @Anonymous ,
Using below M codes:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKlEwVNJRMtQ31DcyMDJUMDSxMjYACpQXAMVjdaBKjNCUmJhhKDFGU2JqSECJqZWhKUSJES6LTK2MjTCUmKAoMbcyxrTIEE0JzC1GuNxiAVdirBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [batchIdentifier = _t, startDate = _t, workplace = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"batchIdentifier", type text}, {"startDate", type datetime}, {"workplace", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"batchIdentifier"}, {{"ALL", each _, type table [batchIdentifier=nullable text, startDate=nullable datetime, workplace=nullable text]}, {"Count", each Table.RowCount(_), Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] <> 1)),
#"Expanded ALL" = Table.ExpandTableColumn(#"Filtered Rows", "ALL", {"startDate", "workplace"}, {"ALL.startDate", "ALL.workplace"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded ALL",{"Count"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"ALL.startDate", "startDate"}, {"ALL.workplace", "workplace"}})
in
#"Renamed Columns"
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Hi @Anonymous ,
Using below M codes:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKlEwVNJRMtQ31DcyMDJUMDSxMjYACpQXAMVjdaBKjNCUmJhhKDFGU2JqSECJqZWhKUSJES6LTK2MjTCUmKAoMbcyxrTIEE0JzC1GuNxiAVdirBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [batchIdentifier = _t, startDate = _t, workplace = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"batchIdentifier", type text}, {"startDate", type datetime}, {"workplace", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"batchIdentifier"}, {{"ALL", each _, type table [batchIdentifier=nullable text, startDate=nullable datetime, workplace=nullable text]}, {"Count", each Table.RowCount(_), Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] <> 1)),
#"Expanded ALL" = Table.ExpandTableColumn(#"Filtered Rows", "ALL", {"startDate", "workplace"}, {"ALL.startDate", "ALL.workplace"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded ALL",{"Count"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"ALL.startDate", "startDate"}, {"ALL.workplace", "workplace"}})
in
#"Renamed Columns"
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Do you mean retain records where the count of batchidentifier is greater than 1 , so keep everything apart from the row for Part4 or is it more complex than this?
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.