Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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")
)
Solved! Go to Solution.
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
@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
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"),
Incredible, thanks for the speedy help! I was trying to complete it all in one step which is where I think it became overly complex.
Once again, thank you!
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 |
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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