Power BI - Split a string input into new rows in a table

user_18059341 0
Viewed 33

I'm working with Dynamics CRM project operations. I am currently putting together a report based on task effort that is being assigned to resources.

The data being pulled contains a 'planned work' column. These are strings with multiple dates and hours included in the payload.

A single cell example is:

[{""End"":""\/Date(1660838400000)\/"",""Hours"":8,""Start"":""\/Date(1660809600000)\/""},{""End"":""\/Date(1660924800000)\/"",""Hours"":9,""Start"":""\/Date(1660892400000)\/""},{""End"":""\/Date(1661184000000)\/"",""Hours"":9,""Start"":""\/Date(1661151600000)\/""}]

What I need to do is, pull the dates and hours for each entry and add it to a new table so they each have their own rows. Example desired output for this cell:

Start Date End Date Hours
1660809600000 1660838400000 8
1660892400000 1660924800000 9
1661151600000 1660924800000 9

The cells can vary in length with multiple entries so it needs to take that into account.

Is there anyone how can point me in the right direction on how this can be done in Power BI?

powerbi-desktop powerbi powerquery dynamics-crm
1 Answers

enter image description here

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiq6OUYpRcs1LAVFWIELfJbEkVcPQzMzAwtjCxAAENPVBEjogwiO/tKgYrNYCzA8uSSwqwabXwNIMSW+tDh57LI1MLHDbY4nfHqBmIu0xNIR6hwx7DA1NDVH8E6sUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"""""","""",Replacer.ReplaceText,{"Column1"}),
    #"Parsed JSON" = Table.TransformColumns(#"Replaced Value",{},Json.Document),
    #"Expanded Column1" = Table.ExpandListColumn(#"Parsed JSON", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"End", "Hours", "Start"}, {"End", "Hours", "Start"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Expanded Column2","/Date(","",Replacer.ReplaceText,{"End", "Start"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",")/","",Replacer.ReplaceText,{"End", "Start"})
in
    #"Replaced Value2"
user_183450370
Related