Forum Discussion
Is this DAX possible in Power Query?
Hi,
I currently have the below DAX to create a table based on certain supplier attributes in another table. Super simple in DAX but I feel due to the volume of records we have this will be far more effient in Power Query M. Is this possible? Every attempt so far has failed to be able to recreate in Power Query.
Any help greatly appreciated!
Views =
UNION(
SELECTCOLUMNS(vwPbiSupplierSummaries,
"PBIGUID", vwPbiSupplierSummaries[PBIGUID],
"SupplierIndex", vwPbiSupplierSummaries[SupplierIndex],
"Order","1",
"Value", "All",
"ColorID", "",
"ColorOrder", "0",
"Attribute", "Overall"),
SELECTCOLUMNS(vwPbiSupplierSummaries,
"PBIGUID", vwPbiSupplierSummaries[PBIGUID],
"SupplierIndex", vwPbiSupplierSummaries[SupplierIndex],
"Order","5",
"Value", vwPbiSupplierSummaries[AvailableDetailLevel],
"ColorID", IF(vwPbiSupplierSummaries[AvailableDetailLevel] = "", "#BBBBB6", IF(vwPbiSupplierSummaries[AvailableDetailLevel] = "1", "#e3c294", IF(vwPbiSupplierSummaries[AvailableDetailLevel] = "2", "#c8914d", IF(vwPbiSupplierSummaries[AvailableDetailLevel] = "3", "#9a655b","")))),
"ColorOrder", IF(vwPbiSupplierSummaries[AvailableDetailLevel] = "", "1.4", IF(vwPbiSupplierSummaries[AvailableDetailLevel] = "1", "1.3", IF(vwPbiSupplierSummaries[AvailableDetailLevel] = "2", "1.2", IF(vwPbiSupplierSummaries[AvailableDetailLevel] = "3", "1.1","1.0")))),
"Attribute", "Detail Level"),
SELECTCOLUMNS(vwPbiSupplierSummaries,
"PBIGUID", vwPbiSupplierSummaries[PBIGUID],
"SupplierIndex", vwPbiSupplierSummaries[SupplierIndex],
"Order","4",
"Value", vwPbiSupplierSummaries[StpCapable],
"ColorID", IF(vwPbiSupplierSummaries[StpCapable] = TRUE, "#C27676", IF(vwPbiSupplierSummaries[StpCapable] = FALSE, "#BBBBB9", "")),
"ColorOrder", IF(vwPbiSupplierSummaries[StpCapable] = TRUE, "2.1", IF(vwPbiSupplierSummaries[StpCapable] = FALSE, "2.2", "2.0")),
"Attribute", "STP Capable"),
SELECTCOLUMNS(vwPbiSupplierSummaries,
"PBIGUID", vwPbiSupplierSummaries[PBIGUID],
"SupplierIndex", vwPbiSupplierSummaries[SupplierIndex],
"Order","2",
"Value", vwPbiSupplierSummaries[Status],
"ColorID", IF(vwPbiSupplierSummaries[Status] = "MATCHED", "#54963F", IF(vwPbiSupplierSummaries[Status] = "UNMATCHED", "#BBBBB7", "")),
"ColorOrder", IF(vwPbiSupplierSummaries[Status] = "MATCHED", "3.1", IF(vwPbiSupplierSummaries[Status] = "UNMATCHED", "3.2", "3.0")),
"Attribute", "Card Accepting"),
SELECTCOLUMNS(vwPbiSupplierSummaries,
"PBIGUID", vwPbiSupplierSummaries[PBIGUID],
"SupplierIndex", vwPbiSupplierSummaries[SupplierIndex],
"Order","3",
"Value", IF(vwPbiSupplierSummaries[PurchaseControlIndicator] = "UNMATCHED", "NO MATCH", vwPbiSupplierSummaries[PurchaseControlIndicator]),
"ColorID", IF(vwPbiSupplierSummaries[PurchaseControlIndicator] = "YES", "#4078B6", IF(vwPbiSupplierSummaries[PurchaseControlIndicator] = "UNMATCHED", "#BBBBB5", IF(vwPbiSupplierSummaries[PurchaseControlIndicator] = "NO", "#90C5E6",""))),
"ColorOrder", IF(vwPbiSupplierSummaries[PurchaseControlIndicator] = "YES", "4.1", IF(vwPbiSupplierSummaries[PurchaseControlIndicator] = "UNMATCHED", "4.3", IF(vwPbiSupplierSummaries[PurchaseControlIndicator] = "NO", "4.2","4.0"))),
"Attribute", "VC Accepting")
)
Hi Anonymous
Place the following M code in a blank query to see the steps. I've only done the first two subtables but for the rest you can apply the same approach. See it all at work in the attached file.
let T1_ = let s0_ = Table.SelectColumns(vwPbiSupplierSummaries, {"PBIGUID", "SupplierIndex"}), s1_ = Table.AddColumn(s0_, "Order", each "1" ), s2_ = Table.AddColumn(s1_, "Value", each "All" ), s3_ = Table.AddColumn(s2_, "ColorID", each "" ), s4_ = Table.AddColumn(s3_, "ColorOrder", each "0" ), s5_ = Table.AddColumn(s4_, "Attribute", each "Overall" ) in s5_, T2_ = let s0_ = Table.SelectColumns(vwPbiSupplierSummaries, {"PBIGUID", "SupplierIndex", "AvailableDetailLevel"}), s1_ = Table.AddColumn(s0_, "Order", each "5" ), s2_ = Table.RenameColumns(s1_, {{"AvailableDetailLevel", "Value"}}), s3_ = Table.AddColumn(s2_, "ColorID", each if vwPbiSupplierSummaries[AvailableDetailLevel] = "" then "#BBBBB6" else if vwPbiSupplierSummaries[AvailableDetailLevel] = "1" then "#e3c294" else if vwPbiSupplierSummaries[AvailableDetailLevel] = "2" then "#c8914d" else if vwPbiSupplierSummaries[AvailableDetailLevel] = "3" then "#9a655b" else ""), s4_ = Table.AddColumn(s3_, "ColorOrder", each if vwPbiSupplierSummaries[AvailableDetailLevel] = "" then "1.4" else if vwPbiSupplierSummaries[AvailableDetailLevel] = "1" then "1.3" else if vwPbiSupplierSummaries[AvailableDetailLevel] = "2" then "1.2" else if vwPbiSupplierSummaries[AvailableDetailLevel] = "3" then "1.1" else "1.0"), s5_ = Table.AddColumn(s4_, "Attribute",each "Detail Level") in s5_, res_ = Table.Combine({T1_,T2_}) in res_Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Anonymous
let T1_ = let s0_ = Table.SelectColumns(vwPbiSupplierSummaries, {"PBIGUID", "SupplierIndex"}), s1_ = Table.AddColumn(s0_, "Order", each "1" ), s2_ = Table.AddColumn(s1_, "Value", each "All" ), s3_ = Table.AddColumn(s2_, "ColorID", each "" ), s4_ = Table.AddColumn(s3_, "ColorOrder", each "0" ), s5_ = Table.AddColumn(s4_, "Attribute", each "Overall" ) in s5_, T2_ = let s0_ = Table.SelectColumns(vwPbiSupplierSummaries, {"PBIGUID", "SupplierIndex", "AvailableDetailLevel"}), s1_ = Table.AddColumn(s0_, "Order", each "5" ), s2_ = Table.AddColumn(s1_, "ColorID", each if [AvailableDetailLevel] = "" then "#BBBBB6" else if [AvailableDetailLevel] = "1" then "#e3c294" else if [AvailableDetailLevel] = "2" then "#c8914d" else if [AvailableDetailLevel] = "3" then "#9a655b" else ""), s3_ = Table.AddColumn(s2_, "ColorOrder", each if [AvailableDetailLevel] = "" then "1.4" else if [AvailableDetailLevel] = "1" then "1.3" else if [AvailableDetailLevel] = "2" then "1.2" else if [AvailableDetailLevel] = "3" then "1.1" else "1.0"), s4_ = Table.RenameColumns(s3_, {{"AvailableDetailLevel", "Value"}}), s5_ = Table.AddColumn(s4_, "Attribute",each "Detail Level") in s5_, res_ = Table.Combine({T1_,T2_}) in res_Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
7 Replies
Hi Anonymous
This should be just as simple in PQ. Share your attempt in PQ and we'll see where it goes wrong.
Table.Combine, Table.AddColumn and Table.SelectColumns and renaming should be most of what you need.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Yes. Probably simpler/less lines of code in M than DAX for this. Please provide sample/mock input data and example of desired output.
Regards,
Pat
Hi Anonymous
Place the following M code in a blank query to see the steps. I've only done the first two subtables but for the rest you can apply the same approach. See it all at work in the attached file.
let T1_ = let s0_ = Table.SelectColumns(vwPbiSupplierSummaries, {"PBIGUID", "SupplierIndex"}), s1_ = Table.AddColumn(s0_, "Order", each "1" ), s2_ = Table.AddColumn(s1_, "Value", each "All" ), s3_ = Table.AddColumn(s2_, "ColorID", each "" ), s4_ = Table.AddColumn(s3_, "ColorOrder", each "0" ), s5_ = Table.AddColumn(s4_, "Attribute", each "Overall" ) in s5_, T2_ = let s0_ = Table.SelectColumns(vwPbiSupplierSummaries, {"PBIGUID", "SupplierIndex", "AvailableDetailLevel"}), s1_ = Table.AddColumn(s0_, "Order", each "5" ), s2_ = Table.RenameColumns(s1_, {{"AvailableDetailLevel", "Value"}}), s3_ = Table.AddColumn(s2_, "ColorID", each if vwPbiSupplierSummaries[AvailableDetailLevel] = "" then "#BBBBB6" else if vwPbiSupplierSummaries[AvailableDetailLevel] = "1" then "#e3c294" else if vwPbiSupplierSummaries[AvailableDetailLevel] = "2" then "#c8914d" else if vwPbiSupplierSummaries[AvailableDetailLevel] = "3" then "#9a655b" else ""), s4_ = Table.AddColumn(s3_, "ColorOrder", each if vwPbiSupplierSummaries[AvailableDetailLevel] = "" then "1.4" else if vwPbiSupplierSummaries[AvailableDetailLevel] = "1" then "1.3" else if vwPbiSupplierSummaries[AvailableDetailLevel] = "2" then "1.2" else if vwPbiSupplierSummaries[AvailableDetailLevel] = "3" then "1.1" else "1.0"), s5_ = Table.AddColumn(s4_, "Attribute",each "Detail Level") in s5_, res_ = Table.Combine({T1_,T2_}) in res_Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Just a quick follow up - I'm not sure the lines below are working as they should as they always hit the final 'else'.
s3_ = Table.AddColumn(s2_, "ColorID", each if vwPbiSupplierSummaries[AvailableDetailLevel] = "" then "#BBBBB6" else if vwPbiSupplierSummaries[AvailableDetailLevel] = "1" then "#e3c294" else if vwPbiSupplierSummaries[AvailableDetailLevel] = "2" then "#c8914d" else if vwPbiSupplierSummaries[AvailableDetailLevel] = "3" then "#9a655b" else ""), s4_ = Table.AddColumn(s3_, "ColorOrder", each if vwPbiSupplierSummaries[AvailableDetailLevel] = "" then "1.4" else if vwPbiSupplierSummaries[AvailableDetailLevel] = "1" then "1.3" else if vwPbiSupplierSummaries[AvailableDetailLevel] = "2" then "1.2" else if vwPbiSupplierSummaries[AvailableDetailLevel] = "3" then "1.1" else "1.0"),
Anonymous
let T1_ = let s0_ = Table.SelectColumns(vwPbiSupplierSummaries, {"PBIGUID", "SupplierIndex"}), s1_ = Table.AddColumn(s0_, "Order", each "1" ), s2_ = Table.AddColumn(s1_, "Value", each "All" ), s3_ = Table.AddColumn(s2_, "ColorID", each "" ), s4_ = Table.AddColumn(s3_, "ColorOrder", each "0" ), s5_ = Table.AddColumn(s4_, "Attribute", each "Overall" ) in s5_, T2_ = let s0_ = Table.SelectColumns(vwPbiSupplierSummaries, {"PBIGUID", "SupplierIndex", "AvailableDetailLevel"}), s1_ = Table.AddColumn(s0_, "Order", each "5" ), s2_ = Table.AddColumn(s1_, "ColorID", each if [AvailableDetailLevel] = "" then "#BBBBB6" else if [AvailableDetailLevel] = "1" then "#e3c294" else if [AvailableDetailLevel] = "2" then "#c8914d" else if [AvailableDetailLevel] = "3" then "#9a655b" else ""), s3_ = Table.AddColumn(s2_, "ColorOrder", each if [AvailableDetailLevel] = "" then "1.4" else if [AvailableDetailLevel] = "1" then "1.3" else if [AvailableDetailLevel] = "2" then "1.2" else if [AvailableDetailLevel] = "3" then "1.1" else "1.0"), s4_ = Table.RenameColumns(s3_, {{"AvailableDetailLevel", "Value"}}), s5_ = Table.AddColumn(s4_, "Attribute",each "Detail Level") in s5_, res_ = Table.Combine({T1_,T2_}) in res_Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Thank you!
Here is the data input and output as it stands via the DAX.
Input data
PBIGUID. SupplierIndex Status AvailableDetailLevel PurchaseControlIndicator StpCapable abcxyz123-4477 4477 MATCHED 1 NO TRUE abcxyz123-4476 4476 MATCHED 1 YES FALSE abcxyz123-4475 4475 UNMATCHED UNMATCHED FALSE abcxyz123-4474 4474 MATCHED 1 NO FALSE abcxyz123-4473 4473 MATCHED 1 NO FALSE abcxyz123-4472 4472 MATCHED 1 NO FALSE Output data
PBIGUID SupplierIndex Attribute Value Order ColorID ColorOrder abcxyz123-4477 4477 Overall All 1 0 abcxyz123-4477 4477 Card Accepting MATCHED 2 #54963F 3.1 abcxyz123-4477 4477 VC Accepting NO 3 #90C5E6 4.2 abcxyz123-4477 4477 STP Capable TRUE 4 #C27676 2.2 abcxyz123-4477 4477 Detail Level 1 5 #e3c294 1.3 abcxyz123-4476 4476 Overall All 1 0 abcxyz123-4476 4476 Card Accepting MATCHED 2 #54963F 3.1 abcxyz123-4476 4476 VC Accepting YES 3 #4078B6 4.1 abcxyz123-4476 4476 STP Capable FALSE 4 #BBBBB9 2.2 abcxyz123-4476 4476 Detail Level 1 5 #e3c294 1.3 abcxyz123-4475 4475 Overall All 1 0 abcxyz123-4475 4475 Card Accepting UNMATCHED 2 #BBBBB7 3.2 abcxyz123-4475 4475 VC Accepting NO MATCH 3 #BBBBB5 4.3 abcxyz123-4475 4475 STP Capable FALSE 4 #BBBBB9 2.2 abcxyz123-4475 4475 Detail Level 5 #BBBBB6 1.4 abcxyz123-4474 4474 Overall All 1 0 abcxyz123-4474 4474 Card Accepting MATCHED 2 #54963F 3.1 abcxyz123-4474 4474 VC Accepting NO 3 #90C5E6 4.2 abcxyz123-4474 4474 STP Capable FALSE 4 #BBBBB9 2.2 abcxyz123-4474 4474 Detail Level 1 5 #e3c294 1.3 abcxyz123-4473 4473 Overall All 1 0 abcxyz123-4473 4473 Card Accepting MATCHED 2 #54963F 3.1 abcxyz123-4473 4473 VC Accepting NO 3 #90C5E6 4.2 abcxyz123-4473 4473 STP Capable FALSE 4 #BBBBB9 2.2 abcxyz123-4473 4473 Detail Level 1 5 #e3c294 1.3 abcxyz123-4472 4472 Overall All 1 0 abcxyz123-4472 4472 Card Accepting MATCHED 2 #54963F 3.1 abcxyz123-4472 4472 VC Accepting NO 3 #90C5E6 4.2 abcxyz123-4472 4472 STP Capable FALSE 4 #BBBBB9 2.2 abcxyz123-4472 4472 Detail Level 1 5 #e3c294 1.3
