CODEWITHSUNDEEP
powerbidynamics-crmpowerquerypowerbi-desktopm
AMD
AMD

Reputation: 25

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

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?

Upvotes: 2

Views: 605

Answers (2)

Muppet Bear
Muppet Bear

Reputation: 152

Adding to @David's answer above, you can also do the two replace value transforms in a single step:

#"Replaced Value1" = Table.ReplaceValue(
        #"Expanded Column2",
        "blah",
        "blah",
        (column_value, new, old) => Text.Select(column_value, {"0".."9"}),
        {"End", "Start"}
    )

Upvotes: 1

davidebacci
davidebacci

Reputation: 30304

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"

Upvotes: 4

Related Questions