Forum Discussion
Help required with dax
Hi, I have a report that involves slots throughout the school day which includes M1 (monday lesson 1), M2, M3, M4,M5,M6,M7, T1,T2,T3,T4 etc.... however we also have other slots which are not numbered such as MA (monday arrival), MB (monday breakfast), ML (monday lunch), TA (tuesday arrival), TB (tuesday breakfast)....etc.
In order to have them in the correct order and to create a clear timeline I've created the following:
= Table.AddColumn(#"Changed Type", "Lesson Index", each let Digits = {"0".."9"},
LessonSplit = Splitter.SplitTextByCharacterTransition(each not List.Contains(Digits,_), each List.Contains(Digits,_))([Lesson]),
DayOfWeek = LessonSplit{0},
Number = Number.From(LessonSplit{1}),
DayOfWeekIndex = if DayOfWeek = "M" then 0 else if DayOfWeek = "T" then 1 else if DayOfWeek = "W" then 2 else if DayOfWeek = "TH" then 4 else 5
in
DayOfWeekIndex * 100 + Number)
Thisa is applying correctly to all actual lessons (M1,M2,M3,M4...F6) but all other slots are coming as error.
Can anyone help with this or suggest a solution?
Thank you
5 Replies
VDS9 , Try using
m
= Table.AddColumn(#"Changed Type", "Lesson Index", each
let
Digits = {"0".."9"},
LessonSplit = Splitter.SplitTextByCharacterTransition(each not List.Contains(Digits,_), each List.Contains(Digits,_))([Lesson]),
DayOfWeek = LessonSplit,
Number = try Number.From(LessonSplit) otherwise null,
DayOfWeekIndex = if DayOfWeek = "M" then 0
else if DayOfWeek = "T" then 1
else if DayOfWeek = "W" then 2
else if DayOfWeek = "TH" then 4
else 5,
SlotIndex = if Number <> null then DayOfWeekIndex * 100 + Number
else if Text.EndsWith([Lesson], "A") then DayOfWeekIndex * 100 - 3
else if Text.EndsWith([Lesson], "B") then DayOfWeekIndex * 100 - 2
else if Text.EndsWith([Lesson], "L") then DayOfWeekIndex * 100 - 1
else null
in
SlotIndex
)Hi VDS9 ,
I think thatt the logic in your Lesson Index column assumes that all Lesson values contain a number after the day identifier (M1, T3). However, slots like MA, MB, ML cause errors since they don't have a number part.
You can modify the formula to handle both numbered and non-numbered slots by assigning a separate index for non-numbered slots.
Please try the bellow Code:= Table.AddColumn( #"Changed Type", "Lesson Index", each let Digits = {"0".."9"}, LessonSplit = Splitter.SplitTextByCharacterTransition(each not List.Contains(Digits,_), each List.Contains(Digits,_))([Lesson]), DayOfWeek = try LessonSplit{0} otherwise null, NumberPart = try Number.From(LessonSplit{1}) otherwise null, DayOfWeekIndex = if DayOfWeek = "M" then 0 else if DayOfWeek = "T" then 1 else if DayOfWeek = "W" then 2 else if DayOfWeek = "TH" then 4 else if DayOfWeek = "F" then 5 else null, // Handle non-numbered slots SlotIndex = if NumberPart = null then if [Lesson] = "MA" then 10 else if [Lesson] = "MB" then 20 else if [Lesson] = "ML" then 30 else if [Lesson] = "TA" then 110 else if [Lesson] = "TB" then 120 else null else DayOfWeekIndex * 100 + NumberPart in SlotIndex )Thanks. I was going to enter the new dax code but there has been a slight change as in the headers. Data is coming from an excel file with different sheets. However although headers are almost the same, the first 3 sheets have 1 less column. For example, first 3 sheets will have: MA,MR,M1,M2,MB,M3,M4,ML,M5,M6,ME,MD and the remaining sheets have MA,MR,M1,M2,M3,MB,M4,M5,ML,M6,M7,ME,MD. Also, Fridays look like this for everyone: FA,FR,F1,F2,FB,F3,F4,FL,FF,FA,FO.
Will I be able to still create something like this? Or due to the slight changes wont be possible?
Thanks
Hi VDS9 ,
The key is to normalize the column headers, meaning you will need to handle the first three sheets (which have fewer columns) and the Friday-specific columns in a way that ensures consistent indexing across all sheets. You can achieve this by checking the column count and adjusting your logic accordingly.
= Table.AddColumn( #"Changed Type", "Lesson Index", each let Digits = {"0".."9"}, // Split the Lesson column by digits and non-digits LessonSplit = Splitter.SplitTextByCharacterTransition(each not List.Contains(Digits,_), each List.Contains(Digits,_))([Lesson]), DayOfWeek = try LessonSplit{0} otherwise null, NumberPart = try Number.From(LessonSplit{1}) otherwise null, DayOfWeekIndex = if DayOfWeek = "M" then 0 else if DayOfWeek = "T" then 1 else if DayOfWeek = "W" then 2 else if DayOfWeek = "TH" then 4 else if DayOfWeek = "F" then 5 else null, // Determine if we are on the first three sheets or the others ColumnCount = Table.ColumnCount(#"Changed Type"), SlotIndex = if ColumnCount = 12 then // Sheets with 1 less column (first 3 sheets) if NumberPart = null then if [Lesson] = "MA" then 10 else if [Lesson] = "MR" then 20 else if [Lesson] = "M1" then 30 else if [Lesson] = "M2" then 40 else if [Lesson] = "MB" then 50 else if [Lesson] = "M3" then 60 else if [Lesson] = "M4" then 70 else if [Lesson] = "ML" then 80 else if [Lesson] = "M5" then 90 else if [Lesson] = "M6" then 100 else if [Lesson] = "ME" then 110 else if [Lesson] = "MD" then 120 else null else DayOfWeekIndex * 100 + NumberPart else if ColumnCount = 13 then // Sheets with all columns (remaining sheets) if NumberPart = null then if [Lesson] = "MA" then 10 else if [Lesson] = "MR" then 20 else if [Lesson] = "M1" then 30 else if [Lesson] = "M2" then 40 else if [Lesson] = "M3" then 50 else if [Lesson] = "MB" then 60 else if [Lesson] = "M4" then 70 else if [Lesson] = "M5" then 80 else if [Lesson] = "ML" then 90 else if [Lesson] = "M6" then 100 else if [Lesson] = "ME" then 110 else if [Lesson] = "MD" then 120 else null else DayOfWeekIndex * 100 + NumberPart else if ColumnCount = 10 then // Fridays with special slots if NumberPart = null then if [Lesson] = "FA" then 10 else if [Lesson] = "FR" then 20 else if [Lesson] = "F1" then 30 else if [Lesson] = "F2" then 40 else if [Lesson] = "FB" then 50 else if [Lesson] = "F3" then 60 else if [Lesson] = "F4" then 70 else if [Lesson] = "FL" then 80 else if [Lesson] = "FF" then 90 else if [Lesson] = "FO" then 100 else null else DayOfWeekIndex * 100 + NumberPart else null // Fallback for unexpected column counts in SlotIndex )
