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 weekdays names, so, Report A is from Monday to Friday, Report B is from Monday to Thursday, and so on and so forth, that is to give you an idea about the entries of this table.

 

Now, what I need to do is to use these columns in the power query to create a new column list that I could expand, and its parameters come from the columns From and To. So, Report A entry for instance should be expanded over 5 rows, Monday, Tuesday, Wednesday, Thursday and Friday, how could I do that?

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

     

3 Replies

  • 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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Worked perfectly, thanks

    • 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