Forum Discussion
Help required with dax
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 )Hi Bibiano_Geraldo thanks for your help. I have entered the code you suggested and although there were no errors detected, the index is now coming as null for every slot.
What do you think the issue is?