Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Convert opening time intervals into columns

Sorry,

in Excel Power Query Editor I need to split up the hours colum into new columns for each weekday:

Monday                         |Tuesday                         |....

10:00-15:00;16:00-22:00|10:00-15:00;16:00-20:00|....

 

out of a list of records like this ones: 

"hours": [
{
"weekday": 1,
"openFrom": "10:00",
"openTil": "15:00"
},
{
"weekday": 1,
"openFrom": "16:00",
"openTil": "22:00"
},
{
"weekday": 2,
"openFrom": "10:00",
"openTil": "15:00"
},
{
"weekday": 2,
"openFrom": "16:00",
"openTil": "20:00"
},

....
]

 

What would be the best approach here? Thanks!

Need Help
Show and Tell

6 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous you should able to use the JSON file as a data source and then in PQ expand/transform to extract the data.

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Sorry, parry2k this will add additional rows. I need to split up the hours list into several columns. One column for each working day with the corresponding time intervalls.

      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        Anonymous once you have rows, you can use pivot to convert to columns 

         

        I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

        ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

         

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end

    Table:

     

    Here are the codes in 'Query Editor'.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSwMjDQNTQFktaGZiC2kRGQVNLBJmMAkbHEJYGpxRCkVik2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Monday = _t, Tuesday = _t, Wednesday = _t, Thursday = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Monday", type text}, {"Tuesday", type text}, {"Wednesday", type text}, {"Thursday", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Name", "Value"),
    Custom1 = Table.TransformColumns(#"Unpivoted Columns",{"Value",each
Text.Split(_,";")
}
),
    #"Added Custom" = Table.AddColumn(Custom1, "Custom", each Record.FromTable(
    let n=[Name] in
    Table.SelectRows(#"Custom1",each [Name]=n)
)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Name", "Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "hours"}})
in
    #"Renamed Columns"

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.