New calculated column using column from another table

[Anonymous]

Hello everyone, 

I am trying to create a new column in my primary table, using some conditions from another table, as well as the primary: 

Primary Table: 

ID	Injury	Transported Boolean
1	A	Transported
2	B	Not Transported
3	B	Transported
4	A	Not Transported
5	A	Transported
6	C	Not Transported

Secondary Table: 

ID	Type
1	a
2	b
3	c
4	d
5	e
6	f

The conditions being: 

If (secondary['Type'] = A, or C, or  D,  or E   OR  primary['Injury'] = A ) AND primary['Transported Boolean] = Transported 

then the new column named primary['Claimable MVA?'] = True, else False 

ID	Injury	Transported Boolean	Claimable MVA?
1	A	Transported	True
2	B	Not Transported	False
3	B	Transported	True
4	A	Not Transported	False
5	A	Transported	True
6	C	Not Transported	False

Would anyone know how to do this? 

Thank you for your time

[edhans · Accepted solution]

Hi @Anonymous ,

You need to merge Table2 into Table1 so it looks like this:

Then add a new custom column with the below code:

if (List.Contains({"a".."e"},[Type]) or [Injury] = "A") and [Transported Boolean] = "Transported" then true else false

 Note that Power Query is case sensitive, so even though in your example above you said "secondary['Type'] = A, or C, or D, or E" your secondary table had a, b, c, d, e, f. They aren't the same to PQ. You can change everything to upper/lowercase with a transformation before you do the merge. Just be aware, a=a, but A<>a.

Then when done, keep all but the temproary Type column. End result:

Total code for the first table, and it assumes your Table2 is exactly as you have it above.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4pCixLzigvyiktQUpVidaCUjoJgTEPvllyigyxlD5dDFTaBmYdNjisMeM6CYMzY9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Injury = _t, #"Transported Boolean" = _t]),
    #"Merged Queries" = Table.NestedJoin(Source, {"ID"}, Table2, {"ID"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Type"}, {"Type"}),
    #"Added Claimable MVA" = Table.AddColumn(#"Expanded Table2", "Claimable MVA", each if (List.Contains({"a".."e"},[Type]) or [Injury] = "A") and [Transported Boolean] = "Transported" then true else false),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Claimable MVA",{"ID", "Injury", "Transported Boolean", "Claimable MVA"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Claimable MVA", type logical}})
in
    #"Changed Type"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[Anonymous]

Hello @edhans , 

Thank you for this! Both of my tables have many more columns than the ones I have listed here, is that ok? 

If i only want to merge 'Type' from Table 2, this is enough? 

Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Type"}, {"Type"})

[edhans]

Yes, when you expand, you expand only the columns you need. You can have 100 columns in Table 2 and only bring in 1, 2, 50, or all 100 columns. Your choice. I recommend you read this article by MS on Merging in Power Query if you want to get a more detailed understanding, or you are unable to see what my code sample is doing. It is for Excel, but Excel and Power BI Power Query are the same when it comes to merges.

[Anonymous]

@edhans I'm very close, I have merged, and what I'm getting stuck on is the code for the new column. What you posted isn't work in the Custon Column query editor. I have gotten this far, but I don't know how to add the AND Transported Boolean=Transported part: 

MVA = if List.Contains({"352", "322", "324", "323", "463"}, [ESO All Incidents.Incident Type Code])= true then "Yes" else if [Injury Primary] = "Motorized Vehicle Accident" = true then "Yes" else "No"

I don't know how to make what I have already created be more of a closed evaluation if (conidtion OR condition) and add the AND condition 

[edhans]

This was the original formual @Anonymous 

if (List.Contains({"a".."e"},[Type]) or [Injury] = "A") and [Transported Boolean] = "Transported" then true else false

You don't need '= true' for List.Contains. It just is true, or false, it doesn't have to equal true. It can, but that is superfluous. Same with the [Injury Primary] = "Motorized Vehicle Accident" = true part. It is just [Injury Primary] = "Motorized Vehicle Accident", no = true for that either.

So change your formula to this:

if List.Contains({"352", "322", "324", "323", "463"}, [ESO All Incidents.Incident Type Code]) and [Injury Primary] = "Motorized Vehicle Accident" then "Yes" else "No"

 Also note that in this formula 352, 322, 324, etc. are text because you put them in quotes. If that is what you want then that works. But if those are numbers, then get rid of the quotes, because 352 will not match "352"

If that isn't right, post some good data and the formula you need, because this no longer matches up with your original request.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[Anonymous]

Hello @edhans , 

In the end this formula worked: 

if (List.Contains({"352", "322", "324", "323", "463"}, [ESO All Incidents.Incident Type Code])= true or [Injury Primary] = "Motorized Vehicle Accident" = true) and [Transported Boolean]="Transported" then "Yes" else "No"

The column that the list.contains is referencing is text. When I editted your formula to fit my column names and values it didn't work so I had to alter it a bit but you provided the logic and backbone, so thank you you helped me a lot

[edhans]

Great @Anonymous - glad I was able to help. Get rid of both of the = true parts though. It should still work, and is easier to read.

if then else needs a true/false answer, so here is what you are doing

• [Injury Primary] = "Motorized Vehicle Accident" = true
• true = true
• true

You only need to know if [Injury Primary] = "Motorized Vehicle Accident", because that evaluates to true if it is motorized vehicle accident. If it doesn't then here is what you are doing:

• [Injury Primary] = "Motorized Vehicle Accident" = true
• false = true
• false

An extra evaluation - though I am not sure the PQ engine is being slowed down by that. 

Same with List.Contains. List.Contains always returns true or false, so no need to see if true/false = true.