index help dax

Question

Can I still use similar code to the below if headers are different on different sheets and also the Friday is different from other 4 days of the week in every sheet. 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(arrival),MR(reading),M1(lesson1),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 looks differentfrom other days of the week for everyone: FA,FR,F1,F2,FB,F3,F4,FL,FF,FA,FO.&nbsp;Trying to have a timeline with lessons and sessions in the right order.Will I be able to still create something like this? Or due to the different headers won’t be possible?Help appreciated. I’m stuck here.&nbsp;Thanks,&nbsp;= Table.AddColumn(&nbsp;&nbsp;&nbsp; #"Changed Type",&nbsp;&nbsp;&nbsp; "Lesson Index",&nbsp;&nbsp;&nbsp; each let&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Digits = {"0".."9"},&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LessonSplit = Splitter.SplitTextByCharacterTransition(each not List.Contains(Digits,_), each List.Contains(Digits,_))([Lesson]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DayOfWeek = try LessonSplit{0} otherwise null,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NumberPart = try Number.From(LessonSplit{1}) otherwise null,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DayOfWeekIndex = if DayOfWeek = "M" then 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else if DayOfWeek = "T" then 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else if DayOfWeek = "W" then 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else if DayOfWeek = "TH" then 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else if DayOfWeek = "F" then 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else null,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; // Handle non-numbered slots&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SlotIndex = if NumberPart = null then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if [Lesson] = "MA" then 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else if [Lesson] = "MB" then 20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else if [Lesson] = "ML" then 30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else if [Lesson] = "TA" then 110&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;else if [Lesson] = "TB" then 120&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else null&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DayOfWeekIndex * 100 + NumberPart&nbsp;&nbsp;&nbsp; in&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SlotIndex)

lbendlin · Answer

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

vds9 · Answer

How can I send a sample without the column "name"? Thats the only column with sensitive information

lbendlin · Answer

Remove that column before posting the data.&nbsp;Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
&nbsp;

vds9 · Answer

You need the data sheet where the data is or the pbix file? Because I have several other sensitive data on linked to the pbix. I will send you the excel

anonymous · Answer

Hi&nbsp;VDS9&nbsp;,
You can&nbsp;provide some&nbsp;raw data&nbsp;in your tables(exclude&nbsp;sensitive&nbsp;data) with&nbsp;Text&nbsp;format&nbsp;and your&nbsp;expected result&nbsp;with backend logic and special examples just as suggested by Ibendlin. It would be helpful to find out the solution. You can refer the following link to share the required info:
How to provide sample data in the Power BI Forum
&nbsp;
And&nbsp;It is better&nbsp;if you can share a&nbsp;simplified&nbsp;pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards

Forum Discussion

index help dax

5 Replies

Related content

Multiple conditions Index

Index

Indexing Not working Properly - Dax Measure

Add calculated index column by DAX

index dax