Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
My table looks like this:
When same values in column path2.1, I need to keep rows that have the highest value in column path 2.2 while keeping the 'Transform File' columns intact.
Any ideas?
Solved! Go to Solution.
Hi @Thimios,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjI0MjJT0lEyMFSK1UEVMYKJGBsYAjnIauAiSGqMDI3R1EBFMNWYKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1.file_path.2.1 = _t, Column1.file_path.2.2 = _t]),
GroupedRows = Table.Group(Source, {"Column1.file_path.2.1"}, {{"Max path 2.2", each Table.MaxN(_, "Column1.file_path.2.2", 1) , type table}}),
Combined = Table.Combine(GroupedRows[Max path 2.2])
in
Combined
Hi all,
I appreciate your assistance.
I ended up creating a duplicate table, keeping only two first columns and grouping as suggested by @dufoq3. Then, I merged the queries using Join Kind = Inner.
@d_rohlfs suggestion works but I was not feeling confident that there will be no issues in the future.
Solution if @dufoq3 is closer to the issue although not easy for us novice users.
Thank you all for helping.
Hey @Thimios ,
You should just be able to do a sort descending on the file path 2.2, then use the remove duplicates function on the file path 2.1 column. Remove duplicates will not remove the first occurence of a value, but any subsequent occurrences.
Hi @Thimios,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjI0MjJT0lEyMFSK1UEVMYKJGBsYAjnIauAiSGqMDI3R1EBFMNWYKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1.file_path.2.1 = _t, Column1.file_path.2.2 = _t]),
GroupedRows = Table.Group(Source, {"Column1.file_path.2.1"}, {{"Max path 2.2", each Table.MaxN(_, "Column1.file_path.2.2", 1) , type table}}),
Combined = Table.Combine(GroupedRows[Max path 2.2])
in
Combined
Thank you @dufoq3 ,
I looked at your advanced editor guide, but this should be done somewhere in the middle of my transformation, not in the souce. How can I make it work?
@Thimios, you probably haven't read all my description from that screenshot. But to put my code into middle of yours you can just copy this part of my code:
GroupedRows = Table.Group(Source, {"Column1.file_path.2.1"}, {{"Max path 2.2", each Table.MaxN(_, "Column1.file_path.2.2", 1) , type table}}),
Combined = Table.Combine(GroupedRows[Max path 2.2])
Change selected Source to your Previous_Step reference, and your 1st next step after my query should refer my last step Combined.