Forum Discussion
Helper IHow to sort based on text column which will change every week
Hi, I am stuck here.
Attached is the matrix i have on PBI.
The column headers are static data entered as a text type, showcasing a week's date range. This data will change every week.
How can i sort it in such a way that (in this example) 30th March - 5th Apr is first, 6th - 12th Apr is second, and then > 13th Apr is third.
I'm really confused, if anyone could provide an alternative to sorting directly also, that would work.
Just need anything that works
see my video
https://1drv.ms/v/s!AiUZ0Ws7G26RhxEUJVRvC1njL21i?e=MSsHdp
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RhxCRRw-4sg9a49rB?e=F4gmfQList.Max( List.Transform( Text.Split([#"ColB - Copy"],"-"),(x)=> Number.From(try Date.FromText(Text.Trim(x), [Format = "dd MMM yy", Culture = "en-EN"])otherwise Date.FromText(Text.Trim(x), [Format = "dd MMM yy", Culture = "en-EN"] ))))
9 Replies
- Ahmedx
Super User
you need to add an index column next to
Column header and it must be dynamic.
write how you enter the name of these columns as text? - Ahmedx
Share sample pbix file to help you.
ambi95Link to Sample Files
Ahmedx please check above link
- Ahmedx
see my video
https://1drv.ms/v/s!AiUZ0Ws7G26RhxEUJVRvC1njL21i?e=MSsHdp
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RhxCRRw-4sg9a49rB?e=F4gmfQList.Max( List.Transform( Text.Split([#"ColB - Copy"],"-"),(x)=> Number.From(try Date.FromText(Text.Trim(x), [Format = "dd MMM yy", Culture = "en-EN"])otherwise Date.FromText(Text.Trim(x), [Format = "dd MMM yy", Culture = "en-EN"] ))))