Forum Discussion

TheTallBloke's avatar
TheTallBloke
New Member
4 years ago
Solved

How to convert date-named columns to individual rows

I'm working with JSON data in PowerBI/Power Query and I get a list of dates in the JSON like below:

 

 

 

 

"dates": {
            "2022-06-17": "1",
            "2022-06-18": "0",
            "2022-06-19": "0",
            "2022-06-20": "1"
        },

 

 

 

 

The dates are currently shown as a field of type "Record", which I can expand, but then I get multiple columns named the same as the date value.  This makes sense but isn't fit for my purpose because the report is using a rolling date range, so today it will result in columns from 2022-06-17 through to 2022-06-20, but next week the "columns" would be 2022-06-21 onwards.

Is there a way to take the "dates" column values (of type record) and convert to multiple rows, duplicating the existing row data?  Ideally, I'd like a column called "Leave_Date" with each of the date values and "Value" with the 1 or 0.

First NameLast Namedates
KlaytonKershawRecord
WalkerBeulerRecord

To become

First NameLast NameLeave_DateValue
KlaytonKershaw2022-06-17..1
KlaytonKershaw2022-06-18..0
KlaytonKershaw2022-06-19..0
KlaytonKershaw2022-06-20..1
WalkerBeuler......

 

I'm happy to do this in multiple steps, and have tried converting the dates JSON value to text and splitting on commas, but nothing seems to work for me.

Any help would be appreciated.

Thanks

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Here I have illustrated how to extract Table from Jason rather than Record and generate the output which you want. See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s5JrCzJz1PSUfJOLSrOSCxXitWJVgpPzMlOLQIKOqWW5gAZsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Name" = _t, #"Last Name" = _t]),
    JsonText = "{
   ""dates"":{
      ""2022-06-17"":""1"",
      ""2022-06-18"":""0"",
      ""2022-06-19"":""0"",
      ""2022-06-20"":""1""
            }
        }",
    TableFromJason = Table.AddColumn(Source, "Custom", each Record.ToTable(Json.Document(JsonText)[dates])),
    #"Expanded Custom" = Table.ExpandTableColumn(TableFromJason, "Custom", {"Name", "Value"}, {"Leave_Date", "Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"First Name", type text}, {"Last Name", type text}, {"Leave_Date", type date}, {"Value", Int64.Type}})
in
    #"Changed Type"

     

     

1 Reply

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Here I have illustrated how to extract Table from Jason rather than Record and generate the output which you want. See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s5JrCzJz1PSUfJOLSrOSCxXitWJVgpPzMlOLQIKOqWW5gAZsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Name" = _t, #"Last Name" = _t]),
    JsonText = "{
   ""dates"":{
      ""2022-06-17"":""1"",
      ""2022-06-18"":""0"",
      ""2022-06-19"":""0"",
      ""2022-06-20"":""1""
            }
        }",
    TableFromJason = Table.AddColumn(Source, "Custom", each Record.ToTable(Json.Document(JsonText)[dates])),
    #"Expanded Custom" = Table.ExpandTableColumn(TableFromJason, "Custom", {"Name", "Value"}, {"Leave_Date", "Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"First Name", type text}, {"Last Name", type text}, {"Leave_Date", type date}, {"Value", Int64.Type}})
in
    #"Changed Type"