Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Creating Changing List of Week Days

Hello everyone, I need a support please.   I got a table that has column for the Report Name, and I got 2 additional columns, the first is called From and the second is called To, both got weekday...
Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkotyC8qMVTSUQouzUtJrAQywlNT8lKLQexYHZgCI6C4bz5UgVtRJqqsMVAwpBSiB8jKKC2CaI8FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ReportName = _t, From = _t, To = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ReportName", type text}, {"From", type text}, {"To", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
dayslist = {"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday","Saturday"},
fromposition = List.PositionOf(dayslist, [From]),
toposition = List.PositionOf(dayslist, [To]),
selectlist = {fromposition..toposition},
final = List.Transform(selectlist, each dayslist{_})

in 
final),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

    Pat

     

mahoneypat's avatar
mahoneypat
Microsoft Employee
4 years ago

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkotyC8qMVTSUQouzUtJrAQywlNT8lKLQexYHZgCI6C4bz5UgVtRJqqsMVAwpBSiB8jKKC2CaI8FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ReportName = _t, From = _t, To = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ReportName", type text}, {"From", type text}, {"To", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
dayslist = {"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday","Saturday"},
fromposition = List.PositionOf(dayslist, [From]),
toposition = List.PositionOf(dayslist, [To]),
selectlist = {fromposition..toposition},
final = List.Transform(selectlist, each dayslist{_})

in 
final),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

Pat

 

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

Nice.

 

In case anyone needs to extend this to handle a situation like Friday through Tuesday or Saturday through Saturday (assuming periods are never longer than this) you can update the custom function as follows:

let
    dayslist = {"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday",
                "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"},
    fromposition = List.PositionOf(dayslist, [From]),
    toposition = List.PositionOf(List.Skip(dayslist, fromposition + 1), [To]) + fromposition + 1,
    selectlist = {fromposition..toposition},
    final = List.Transform(selectlist, each dayslist{_})
in 
    final