Retrieve value from queries if condition applies

[KelvinMorel]

Hi,

I have 2 queries (NL Sales ZIP) and (NL ZIP list), in this 2nd query I have 3 columns (“ZIP_START”, “ZIP_END” and “TOWN”). I would like to retrieve “TOWN” value from (NL ZIP list) if the value of (NL Sales ZIP) is between “ZIP_START” and “ZIP_END” value of (NL ZIP list) query.

This is way simplified but it’s something like this I should have, I guess:

= Merge queries if value “X” is between “Y” and “Z”

Attached to examples of possible queries.

NL Sales ZIP

 

NL ZIP list

[Ola_S]

See solution above. Here adjusted for educationl purpose:

let
 Table1 = Table.TransformColumnTypes(
Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrKwMFTSUXJyVPBLLclILcpJzEspVorVAcoYmoJkQoIxZMzMzIyAMgGeqDKxAA==", BinaryEncoding.Base64), Compression.Deflate)), 
let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ZIP Num" = _t, #"ZIP Name" = _t])
,{{"ZIP Num", Int64.Type}}),
 Table2 = Table.TransformColumnTypes(
Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTK0tLQEUo65xSWpRSmJuUqxOtFKRhA5IxOwXFh+TmoeXMoUIgXRFlWemJdalFRalA6WNIboM4VI+iQWpeaBxc2gdhlAaMec3NSiVKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), 
let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ZIP Start" = _t, #"ZIP End" = _t, #"ZIP Town" = _t])
,{{"ZIP Start", Int64.Type}, {"ZIP End", Int64.Type}}),
 #"Merge if > and <" = Table.AddColumn(Table1, "RelativeJoin", 
 (Earlier) => Table.SelectRows(Table2, 
 each Earlier[ZIP Num] >= [ZIP Start] and 
 Earlier[ZIP Num] <= [ZIP End] ) ),
 #"Expanded RelativeJoin" = Table.ExpandTableColumn(#"Merge if > and <", "RelativeJoin", {"ZIP Town"}, {"ZIP Town"})
in #"Expanded RelativeJoin"

Reference: https://community.powerbi.com/t5/Desktop/joinAlgorithm-and-keyEqualityComparers/td-p/110108

[Ashish_Mathur · Accepted solution]

Hi,

In the NL Sales ZIP Table, add this calculated column formula

=CALCULATE(VALUES('NL Zip List'[TOWN]),FILTER('NL Zip List','NL Zip List'[ZIP Start]<=[ZIPNUM]&&'NL Zip List'[ZIP End]>=[ZIPNUM]))

Hope this helps.

[Carlucha28]

Hi there,

I think I have a somewhat similar ask. I'm very new to PBI and DAX so I'm getting lost in the logic but think this can be done! 

I want to add a column that returns a numeric value (either 1/0) based on a text value from another query. The two queries contain a unique site ID.

For example, my Query1 contains all my site data and Query2 contains all my project data (5 project types) that occured at those sites. I want to add 5 columns (named for each project type) to Query1 so I can easily summarize what activity has occured at each unique site. 

Query2:

SiteABC, Project Type1

SiteABC, Project Type2

SiteDEF, Project Type5

SiteDEF, Project Type3

Query1:

Site              ProjectType1    ProjectType2    ProjectType3     ProjectType4     ProjectType5

SiteABC             1                               1

SiteDEF                                                                    1                                                1

SiteGHI

SiteJKL

Any help is so very appreciated!