Forum Discussion
Alphabetical 'between' argument?
I've a data set where categories in a column have letter prefixes (e.g. A. Butter, B. Milk, C. Yogurt), and then are summarised by a total for those categories in another row (e.g. 'Dairy'). What i want to do (either through Power Query or DAX) is to construct an argument and create a column like below, to show which main category each subcategory actually belongs to (pseudo-code):
If [Product letter] >= "A" and [Product letter] <= "C" then "Dairy" else
If [Product letter] >= "D" and [Product letter] <= "F" then "Meat" etc etc
I've seperated out the letters, but does PBI have this kind of text operator - to recognise that 'C' comes after 'B' for example?
Thanks
hI GlassShark1
Download this sample PBIX file
You can create a column using this DAX
SubCat = SWITCH( TRUE(), MID([Category], 1,1) IN {"A", "B", "C"}, "Dairy", MID([Category], 1,1) IN {"D", "E", "F"}, "Meat", "Fruit" )Regards
Phil
Hi GlassShark1
Please @ mention me in replies or I won't be notified of your reply. Type @ then select my name.
MID is a bit of a misleading in this case as it's returning the first character from the string, so just A, B etc. This is required to check against the list of characters with the IN operator.
SWITCH acts like a multi-nested IF. Using TRUE as the 1st expression allows you to then match any subsequent expressions, and if they evaluate to TRUE then that result is chosen. In this case I'm checking the first letter from each string and when a match is found, the appropriate sub-cat string is returned.
Please see fiel linked to above for a Power Query solution.
The code for this is in the Table2 query. It works in a similar way to the DAX solution, getting the first character from the Category and checking that against a string e.g."ABC" then returning the appropriate Subcat.
regards
Phil
Eventually figured it out in Power Query - something like the below works with lists:
Dairy_List = {"A".."C"},
Meat_List = {"D".."E"},
<rest of the code>
#"Stepforthis" = Table.AddColumn(#"LastStep", "Main Category", each
if List.Contains(Dairy_List,[Letter]) then "Dairy" else
if List.Contains(Meat_List,[Letter]) then "Meat" else
"Whatever"
)
Thanks 🙂
6 Replies
hI GlassShark1
Download this sample PBIX file
You can create a column using this DAX
SubCat = SWITCH( TRUE(), MID([Category], 1,1) IN {"A", "B", "C"}, "Dairy", MID([Category], 1,1) IN {"D", "E", "F"}, "Meat", "Fruit" )Regards
Phil
Brilliant - i did think about lists but really not sure about them in PBI yet. Thanks 🙂
I'm just trying to break this down so i understand it. Does the MID argument add anything? Seems fine without it?
I've never used SWITCH before - the docs seem to say it basically uses comma seperated expressions then the outomes if those expressions are true which makes sense. What is the TRUE() element doing at the begining though? I would read this as 'expression1', so i'm not sure how it works...
Annoyingly i've now twigged i probably need a solution in Power Query anyway, as i need to use the outcomes of this to make a DIM table...this will doubtless be useful though - thank you!
Hi GlassShark1
Please @ mention me in replies or I won't be notified of your reply. Type @ then select my name.
MID is a bit of a misleading in this case as it's returning the first character from the string, so just A, B etc. This is required to check against the list of characters with the IN operator.
SWITCH acts like a multi-nested IF. Using TRUE as the 1st expression allows you to then match any subsequent expressions, and if they evaluate to TRUE then that result is chosen. In this case I'm checking the first letter from each string and when a match is found, the appropriate sub-cat string is returned.
Please see fiel linked to above for a Power Query solution.
The code for this is in the Table2 query. It works in a similar way to the DAX solution, getting the first character from the Category and checking that against a string e.g."ABC" then returning the appropriate Subcat.
regards
Phil
Thank you PhilipTreacy - learned alot from you this morning! Wouldn't all SWITCH formulas start with TRUE() then? Still wrestling with this bit. Maybe i'm struggling because it's a boolean? I'll keep trying to figure it out anyway - thanks again 🙂
Hi GlassShark1
Not all SWITCH use TRUE() but you see it a lot.
You could use SWITCH to test a value e.g.
SWITCH( MyVal, 1, "Alpha", 2, "Beta", 3, "Gamma", 0 )Regards
Phil
Eventually figured it out in Power Query - something like the below works with lists:
Dairy_List = {"A".."C"},
Meat_List = {"D".."E"},
<rest of the code>
#"Stepforthis" = Table.AddColumn(#"LastStep", "Main Category", each
if List.Contains(Dairy_List,[Letter]) then "Dairy" else
if List.Contains(Meat_List,[Letter]) then "Meat" else
"Whatever"
)
Thanks 🙂