Forum Discussion

jaryszek's avatar
jaryszek
Super User
1 year ago
Solved

Change visual sorting based on weeks

hi Guys, I have visual which should be sorted from Thuesday, not Friday (visual by WeekDays. WeekOfMonth: Week 1, Week 2, Week 3, Week 4-->WeekDay).  This is the order which i w...
Need Help
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    Hi jaryszek ,

    Please follow below steps.

    1. In Query editor, I have created new column (Data-Copy) based on
    'Date' column in 'Dim_Date' table.

    2. I have done some transformation on (Data-Copy) like change the language and datatype, created custom column with weekday values.

    3. I have created table (table) with Weekday and Sort columns.
    and merged the new table with existing 'Dim_Date' table. and i have expanded the table and removed blank columns.

    4. You can see the 'APPLIED STEPS' in Query editor, it will show each and every steps. Please refer below snap.

     

     

    If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
    Thank you.

v-dineshya's avatar
v-dineshya
Community Support
1 year ago

Hi jaryszek ,

Thank you for reaching out to the Microsoft Community Forum.

 

You want your visual to show weekdays in correct calendar order for each WeekOfMonth based on actual date sequence, not default weekday names or alphabetically.

 

Please follow below steps to fix the issue.

 

1. Created table (Table) with below M code in Query editor.

 

let
Source = Table.FromRows({
{"2025-04-01", "Tuesday", "Week 1"},
{"2025-04-02", "Wednesday", "Week 1"},
{"2025-04-03", "Thursday", "Week 1"},
{"2025-04-04", "Friday", "Week 1"},
{"2025-04-05", "Saturday", "Week 1"},
{"2025-04-06", "Sunday", "Week 1"},
{"2025-04-07", "Monday", "Week 2"},
{"2025-04-08", "Tuesday", "Week 2"},
{"2025-04-09", "Wednesday", "Week 2"},
{"2025-04-10", "Thursday", "Week 2"},
{"2025-04-11", "Friday", "Week 2"},
{"2025-04-12", "Saturday", "Week 2"},
{"2025-04-13", "Sunday", "Week 2"}
}, {"Date", "WeekDay", "WeekOfMonth"}),

ChangedTypes = Table.TransformColumnTypes(Source,{
{"Date", type date}, {"WeekDay", type text}, {"WeekOfMonth", type text}
})
in
ChangedTypes

 

Please refer snap.

 

 

2.  Created sorting columns in "Table" View. Please refer snap

 

SortIndex for sorting by date:  SortIndex = RANKX(ALL('Table'), 'Table'[Date], , ASC)

Composite key for unique sorting:  WeekDay_Composite = 'Table'[WeekOfMonth] & " - " & 'Table'[WeekDay]

DisplayWeekDay = 'Table'[WeekDay]

 

 

3. Sorted Composite column. refer in snap.

Select WeekDay_Composite column --> Sort by Column --> choose SortIndex.

Now each WeekDay_Composite ("Week 1 - Tuesday") is sorted by actual date.

 

 

4. In visual, Drag the field "WeekDay_Composite" in X-axis and measure "Sum of CostInBillingCurrency" in Y-axis.

Please refer attached PBIX file.

 

 

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

 

 

 

 

  • jaryszek's avatar
    jaryszek
    Super User
    1 year ago

    Ok attached example worked.

    I tried similar approach with Weeks:
    Added WekDayKey:

         AddingWeekDayKey = Table.AddColumn(
    #"Added Adjusted_DayOfWeek_Sort",
    "WeekDayKey",
    each [WeekOfMonth] & "-" & Text.From([Day]))


    and sort by SortIndex column. 

    But i do not want to put my WeekDayKey into visual -> only i want to put there weeks like Week1, Week2. 
    The same for WeekDay_Composite

    How to woraround it? 

    Best,
    Jacek




    • v-dineshya's avatar
      v-dineshya
      Community Support
      1 year ago

      Hi jaryszek ,

      You want to sort by a hidden composite key (like WeekDayKey or WeekDay_Composite), but display only WeekDay in the visual and do this dynamically for each selected WeekOfMonth ( Tuesday --> Wednesday for Week 1, Monday --> Tuesday for Week 2, etc).

      Please follow below steps.

      1. Created a composite label column:

      This will be used in the X-axis instead of plain WeekDay:

      DisplayLabel = 'Table'[WeekOfMonth] & "-" & 'Table'[WeekDay]


      2. Created a numeric sort key

      SortIndex = VALUE(FORMAT('Table'[Date], "YYYYMMDD"))

      Note: This gives you a unique number for sorting in true calendar order.

      3. Sort DisplayLabel by SortIndex

      In Data view: Select the DisplayLabel column and Click “Sort by column” --> click SortIndex

      Note: Now your labels are sorted correctly in the visual.

       

      If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

      Thank you

       

      • jaryszek's avatar
        jaryszek
        Super User
        1 year ago

        Thanks is still not working:

         



        Let me copy my Dim_Table here. 
        DateDateKeyDayOfYearFiscalQuarterFiscalYearIsHolidayIsWeekendMonthMonthNoMonthShortQueryNoWeekDayWeekNoYearYearMonthYearQuarterDayWeekOfMonthWeekDayNumberWeekOfMonth_SortMinDateInWeekWeekDay_SortDisplayLabelSortIndex

        wtorek, 1 kwietnia 2025202504019122025FalseFalseApril4Apr4Tuesday1420252025-0421Week 121wtorek, 1 kwietnia 202511Week 1-Tuesday20250401
        środa, 2 kwietnia 2025202504029222025FalseFalseApril4Apr4Wednesday1420252025-0422Week 131wtorek, 1 kwietnia 202512Week 1-Wednesday20250402
        czwartek, 3 kwietnia 2025202504039322025FalseFalseApril4Apr4Thursday1420252025-0423Week 141wtorek, 1 kwietnia 202513Week 1-Thursday20250403
        piątek, 4 kwietnia 2025202504049422025FalseTrueApril4Apr4Friday1420252025-0424Week 151wtorek, 1 kwietnia 202514Week 1-Friday20250404
        sobota, 5 kwietnia 2025202504059522025FalseTrueApril4Apr4Saturday1420252025-0425Week 161wtorek, 1 kwietnia 202515Week 1-Saturday20250405
        niedziela, 6 kwietnia 2025202504069622025FalseFalseApril4Apr4Sunday1420252025-0426Week 171wtorek, 1 kwietnia 202516Week 1-Sunday20250406
        poniedziałek, 7 kwietnia 2025202504079722025FalseFalseApril4Apr4Monday1520252025-0427Week 111wtorek, 1 kwietnia 202517Week 1-Monday20250407
        wtorek, 8 kwietnia 2025202504089822025FalseFalseApril4Apr4Tuesday1520252025-0428Week 222wtorek, 8 kwietnia 202521Week 2-Tuesday20250408
        środa, 9 kwietnia 2025202504099922025FalseFalseApril4Apr4Wednesday1520252025-0429Week 232wtorek, 8 kwietnia 202522Week 2-Wednesday20250409
        czwartek, 10 kwietnia 20252025041010022025FalseFalseApril4Apr4Thursday1520252025-04210Week 242wtorek, 8 kwietnia 202523Week 2-Thursday20250410
        piątek, 11 kwietnia 20252025041110122025FalseTrueApril4Apr4Friday1520252025-04211Week 252wtorek, 8 kwietnia 202524Week 2-Friday20250411
        sobota, 12 kwietnia 20252025041210222025FalseTrueApril4Apr4Saturday1520252025-04212Week 262wtorek, 8 kwietnia 202525Week 2-Saturday20250412
        niedziela, 13 kwietnia 20252025041310322025FalseFalseApril4Apr4Sunday1520252025-04213Week 272wtorek, 8 kwietnia 202526Week 2-Sunday20250413
        poniedziałek, 14 kwietnia 20252025041410422025FalseFalseApril4Apr4Monday1620252025-04214Week 212wtorek, 8 kwietnia 202527Week 2-Monday20250414
        wtorek, 15 kwietnia 20252025041510522025FalseFalseApril4Apr4Tuesday1620252025-04215Week 222wtorek, 8 kwietnia 202521Week 2-Tuesday20250415
        środa, 16 kwietnia 20252025041610622025FalseFalseApril4Apr4Wednesday1620252025-04216Week 333środa, 16 kwietnia 202531Week 3-Wednesday20250416
        czwartek, 17 kwietnia 20252025041710722025FalseFalseApril4Apr4Thursday1620252025-04217Week 343środa, 16 kwietnia 202532Week 3-Thursday20250417
        piątek, 18 kwietnia 20252025041810822025FalseTrueApril4Apr4Friday1620252025-04218Week 353środa, 16 kwietnia 202533Week 3-Friday20250418
        sobota, 19 kwietnia 20252025041910922025FalseTrueApril4Apr4Saturday1620252025-04219Week 363środa, 16 kwietnia 202534Week 3-Saturday20250419
        niedziela, 20 kwietnia 20252025042011022025FalseFalseApril4Apr4Sunday1620252025-04220Week 373środa, 16 kwietnia 202535Week 3-Sunday20250420
        poniedziałek, 21 kwietnia 20252025042111122025FalseFalseApril4Apr4Monday1720252025-04221Week 313środa, 16 kwietnia 202536Week 3-Monday20250421
        wtorek, 22 kwietnia 20252025042211222025FalseFalseApril4Apr4Tuesday1720252025-04222Week 323środa, 16 kwietnia 202537Week 3-Tuesday20250422
        środa, 23 kwietnia 20252025042311322025FalseFalseApril4Apr4Wednesday1720252025-04223Week 434środa, 23 kwietnia 202541Week 4-Wednesday20250423
        czwartek, 24 kwietnia 20252025042411422025FalseFalseApril4Apr4Thursday1720252025-04224Week 444środa, 23 kwietnia 202542Week 4-Thursday20250424
        piątek, 25 kwietnia 20252025042511522025FalseTrueApril4Apr4Friday1720252025-04225Week 454środa, 23 kwietnia 202543Week 4-Friday20250425
        sobota, 26 kwietnia 20252025042611622025FalseTrueApril4Apr4Saturday1720252025-04226Week 464środa, 23 kwietnia 202544Week 4-Saturday20250426
        niedziela, 27 kwietnia 20252025042711722025FalseFalseApril4Apr4Sunday1720252025-04227Week 474środa, 23 kwietnia 202545Week 4-Sunday20250427
        poniedziałek, 28 kwietnia 20252025042811822025FalseFalseApril4Apr4Monday1820252025-04228Week 414środa, 23 kwietnia 202546Week 4-Monday20250428
        wtorek, 29 kwietnia 20252025042911922025FalseFalseApril4Apr4Tuesday1820252025-04229Week 424środa, 23 kwietnia 202547Week 4-Tuesday20250429
        środa, 30 kwietnia 20252025043012022025FalseFalseApril4Apr4Wednesday1820252025-04230Week 434środa, 23 kwietnia 202541Week 4-Wednesday20250430

         

        Put this table to chat gpt, write prompt: "make for me a formatted table from it to copy to excel" and you will get my Dim_date table. 

        Why I am getting this error?

        And second question is: How to hidden displayKey on the visual? 

         

        Best,
        Jacek