Forum Discussion
Join on multiple columns using Power query
Anonymous I think you might be trying to apply the logic of some other app you are using to Power BI, and that will not work. You don't actually create JOINS in the Power BI data model. You do in Power Query, but not in DAX. In DAX they are filter relationships, and they either equal or they don't.
However, once you have a filter relationship, you can apply other logic in your formulas (measures) to modify how they work. For example, you might have the following to show cumulative sales through a date on your visual:
Sales Cumulative = VAR varCurrentDate = MAX( Sales[Date] ) RETURN CALCULATE( [Sales], Date[Date] <= varCurrentDate, REMOVEFILTERS( Dates[Date] ) )That will cause the filter to act in a way that will get all sales prior to today through today.
But that is not a merge operation.
Does that make sense?
Perhaps tell us what your end goal is, and not ask us to translate Program A logic into Power BI logic. It can cause us to come at this totally wrong, which I've certianly being doing so far. 😁
Anonymous - you do not need to concatenate in Power Query to join on multiple columns. In the Merge box, just select your first column, then CTRL-CLICK on the 2nd, 3rd, etc.
It will put numbers next to the columns in the order you click.
THen in your 2nd table, click on the same columns you want to merge on in the same order.
There is not a way to join based on <= using the merge feature. It only supports:
- left/right join
- inner join
- outer join
- left/right anti-join
You can do a cartesian join too outside of the merge tool.
You can do it though with a Table.SelectRows(). It may not perform well on large data sets. Consider you have two tables:
One called Table:
One called Other Data:
The following code, using the Table.SelectRows() function below will "merge" the [Other Data] table into the [Table] table when Data from Other Data is <= Data from Table.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzMGkOJi3ApKVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", Int64.Type}}),
#"Other Merge" =
Table.AddColumn(
#"Changed Type",
"Other Merge",
each
let
varData = [Data]
in
Table.SelectRows(
#"Other Data",
each [Data] <= varData
)
),
#"Expanded Other Merge" = Table.ExpandTableColumn(#"Other Merge", "Other Merge", {"Codes"}, {"Codes"})
in
#"Expanded Other Merge"You can see my PBIX here if you want to play with it.