Expand array to columns

Hey Anonymous ,

   Similar to what Pragati11  had mentioned - it is difficult to really assess your solution without some more details on how this data is structued and being loaded.

   That being said, is this is just coming through as a single cell/array and the data you provided is static this might be a workable solution for you

So right now we are starting with this data

We can split the data at each day using this delimite in Power Query

Add each column as a unique query using "Add as New Query"

Here is if we use column 2 as an example

Convert to a table with comma as the delimiter

We can then do a few custom columns

Start Time

Text.AfterDelimiter([Column1], ":")

End Time

Text.AfterDelimiter([Column2], ":")

Weekday

Text.AfterDelimiter([Column2], ":")

Then we can remove the old columns

And remove the brackets from the Weekday column. You will now have this data left

You can then take all of the steps from the advanced editor and copy and paste the other columns from your original table savings you some work

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiq6OUcrILy0qjlGyArGLSxKLSkIyc1OB/BglQ2MrAwMgilHSiVFKzUtBSJgjSZSnpmanJFaCjYhR8vX3i1GKrdXBNMyAdMNCQl2pZ1i4qwsVXeYRSqJhRoY4DXML8qSey4IdQ3AYZkSGYaHg2ATCKqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test_Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test_Data", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Test_Data", Splitter.SplitTextByDelimiter("},", QuoteStyle.Csv), {"Test_Data.1", "Test_Data.2", "Test_Data.3", "Test_Data.4", "Test_Data.5", "Test_Data.6", "Test_Data.7"}),
    #"Test_Data 3" = #"Split Column by Delimiter"[Test_Data.2],
    #"Converted to Table" = Table.FromList(#"Test_Data 3", Splitter.SplitTextByDelimiter(","), null, null, ExtraValues.Error),
    #"Changed Type1" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "StartTime", each Text.AfterDelimiter([Column1], ":")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "EndTime", each Text.AfterDelimiter([Column2], ":")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Weekday", each Text.AfterDelimiter([Column3], ":")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Column1", "Column2", "Column3"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","[","",Replacer.ReplaceText,{"Weekday"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","]","",Replacer.ReplaceText,{"Weekday"})
in
    #"Replaced Value1"

Once you've done that with each weekday, go ahead and append your data and it will look like this (I only did Tues and Wednesday)

I realize this is a clunky solution but, let us know if it works. If not we can keep noodling away at it!