cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User IV
Super User IV

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


Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.