cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
steve_e
Frequent Visitor

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")   
    )

 

 

2 ACCEPTED SOLUTIONS
AlB
Super User III
Super User III

Hi @steve_e 

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 

SU18_powerbi_badge

View solution in original post

AlB
Super User III
Super User III

@steve_e 

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 

 

SU18_powerbi_badge

View solution in original post

7 REPLIES 7
AlB
Super User III
Super User III

@steve_e 

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 

 

SU18_powerbi_badge

View solution in original post

AlB
Super User III
Super User III

Hi @steve_e 

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 

SU18_powerbi_badge

View solution in original post

steve_e
Frequent Visitor

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"),
           

  

steve_e
Frequent Visitor

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!

steve_e
Frequent Visitor

Thank you!

 

Here is the data input and output as it stands via the DAX. 

 

Input data

 

PBIGUID.   SupplierIndexStatusAvailableDetailLevelPurchaseControlIndicatorStpCapable
abcxyz123-44774477MATCHED1NOTRUE
abcxyz123-44764476MATCHED1YESFALSE
abcxyz123-44754475UNMATCHED UNMATCHEDFALSE
abcxyz123-44744474MATCHED1NOFALSE
abcxyz123-44734473MATCHED1NOFALSE
abcxyz123-44724472MATCHED1NOFALSE

 

Output data

 

PBIGUIDSupplierIndexAttributeValueOrderColorIDColorOrder
abcxyz123-44774477OverallAll1 0
abcxyz123-44774477Card AcceptingMATCHED2#54963F3.1
abcxyz123-44774477VC AcceptingNO3#90C5E64.2
abcxyz123-44774477STP CapableTRUE4#C276762.2
abcxyz123-44774477Detail Level15#e3c2941.3
abcxyz123-44764476OverallAll1 0
abcxyz123-44764476Card AcceptingMATCHED2#54963F3.1
abcxyz123-44764476VC AcceptingYES3#4078B64.1
abcxyz123-44764476STP CapableFALSE4#BBBBB92.2
abcxyz123-44764476Detail Level15#e3c2941.3
abcxyz123-44754475OverallAll1 0
abcxyz123-44754475Card AcceptingUNMATCHED2#BBBBB73.2
abcxyz123-44754475VC AcceptingNO MATCH3#BBBBB54.3
abcxyz123-44754475STP CapableFALSE4#BBBBB92.2
abcxyz123-44754475Detail Level 5#BBBBB61.4
abcxyz123-44744474OverallAll1 0
abcxyz123-44744474Card AcceptingMATCHED2#54963F3.1
abcxyz123-44744474VC AcceptingNO3#90C5E64.2
abcxyz123-44744474STP CapableFALSE4#BBBBB92.2
abcxyz123-44744474Detail Level15#e3c2941.3
abcxyz123-44734473OverallAll1 0
abcxyz123-44734473Card AcceptingMATCHED2#54963F3.1
abcxyz123-44734473VC AcceptingNO3#90C5E64.2
abcxyz123-44734473STP CapableFALSE4#BBBBB92.2
abcxyz123-44734473Detail Level15#e3c2941.3
abcxyz123-44724472OverallAll1 0
abcxyz123-44724472Card AcceptingMATCHED2#54963F3.1
abcxyz123-44724472VC AcceptingNO3#90C5E64.2
abcxyz123-44724472STP CapableFALSE4#BBBBB92.2
abcxyz123-44724472Detail Level15#e3c2941.3
mahoneypat
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


AlB
Super User III
Super User III

Hi @steve_e 

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 

 

SU18_powerbi_badge

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors