Convert opening time intervals into columns

[kafisch]

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!

[v-alq-msft]

Hi, @kafisch 

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.

[parry2k]

@kafisch 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.⚡

[kafisch]

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]

@kafisch 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.⚡