Reply
tamim
Frequent Visitor

find matching rows and sort them

Hello,

 

you can see in the attached picture 2 columns, that I want to comapre.

but I want to loop thorough them.

So, I want to check if the  number in [end_zipcode] is exist in [Station_zipcode].

 

And if it is found, I want to orgnize them on the same row. like the other picture

.Capture.PNG

 

power bi.PNG

1 REPLY 1
mahoneypat
Microsoft Employee
Microsoft Employee

Normally, I would suggest you do this with DAX, but given your desired output I used query instead.  Is this what you are looking for?

 

zipcode.png

 

If so, you can achieve it with this M code.  Just copy/paste it into a blank query and Advanced Editor.

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0NFbSgVCxOmC+JYRvCeYbGZgYAPlgCixvYAxWD6Kg6k0g6k1gfAsI30IpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [end_zipcode = _t, Station_zipcode = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"end_zipcode", type text}, {"Station_zipcode", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #"Changed Type"[Station_zipcode]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Station_zipcode"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "Station_zipcode"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Station_zipcode", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Compare", each if [end_zipcode]=[Station_zipcode] then "True" else "False"),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Compare", type text}})
in
#"Changed Type2"

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)