Sample File attached. Results sheet shows Desired Result, Current PQ Result and space for Dynamic PQ Result.
I need to add a column to the Table on the Data sheet. I can do this via the PQ in the Sample file using
= Table.AddColumn
(ChangeType, "Trans_Code", each if [Transmission] = "Manual" then "MTM " & [Gears] & "Sp"
else if [Transmission] = "Automatic" then "ATM"
else if [Transmission] = "Semi-Automatic" then "Semi"
else if [Transmission] = "CVT" then "CVT"
else if [Transmission] = "Direct Drive" then "Direct"
else null)
This works fine, except that both the if condition value and the else required value are hard coded. However, it is possible that some time in the future a new "Transmission Type" might be needed.
So I created the "tblTrans" Table on the Trans Code sheet. Then that new type can be added to the Table and that new value needs to be carried through to the PQ if statement. In other words the PQ needs to be dynamic, using the tblTrans Table values rather than have the values hard coded.
I used PQ to create a "List of Lists" from the tblTrans, where each inner List is a List of the Transmission_Type and the Code for that Transmission_Type with:
= Table.ToColumns(Table.Transpose(ChangeType))
Then I got stuck as to how to use that to replace the hard coding.
Any help greatly appreciated.
A simple merge query would be an easy solution.
Thanks Velouria.
I had already considered that and tried it. However that does not add the No. of Gears and "Sp" to all the "MTM"s,
I would still need if....else to do that and that takes us back to the original issue on needing that to be dynamic and not hard coded.
I'd just add that as a separate step afterwards since it only applies to Manual.
Yes I could do that, and just hope that the need to add the No of gears and "Sp" always applies only to MTM and does not change to being needed for example "ATM too in the future.
However, having it fully dynamic would be ideal. Adding another column to the tblTrans Table headed "Include Gears" and values of "yes" or "no" for each Transmission_Type could be added.
That would also work as you can then just merge that column in too, and check it in the next step.
Thanks again Velouria.
I did that and tested by changing "Include Gears" from "No" to "Yes" for Automatic, and adding a new Transmission_Type "Indirect Drive" with "Indirect" as the Code and "N/A" for Gears and "No" for Include.
All worked perfectly and the result is fully dynamic.
Final M Code
let
Source = Excel.CurrentWorkbook(){[Name="tblModels"]}[Content],
ChangeType = Table.TransformColumnTypes(Source,{{"Model", type text}, {"Transmission", type text}, {"Gears", type text}}),
Merge = Table.NestedJoin(ChangeType, {"Transmission"}, tblTrans, {"Transmission_Type"}, "tblTrans", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Merge, "tblTrans", {"Code", "Include Gears"}, {"Code", "Include Gears"}),
AddTransCode = Table.AddColumn(Expand, "Trans_Code", each if [Include Gears] = "Yes" then
& " " & [Gears] & "Sp" else),
RemCols = Table.RemoveColumns(AddTransCode,{"Code", "Include Gears"})
in
RemCols
