Results 1 to 5 of 5

Thread: Transforming Time Series JSON Data with Power Query

  1. #1
    Neophyte rlenterprises66's Avatar
    Join Date
    Jul 2019
    Posts
    2
    Articles
    0
    Excel Version
    Excel for Office 365

    Transforming Time Series JSON Data with Power Query



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,
    First post here!

    I'm pulling my hair out trying to transform some time series data from a JSON file using Power Query. I have attached the JSON file of interest (note that I had to add .txt onto the end to get it to upload here).

    This file contains 4 Items which each have 2 paired timestamps / data points. The timestamps are (and always will be) the same for all items. Ideally, I'd like to transform this JSON file into a table as follows:

    Timestamp Testtag DST_Test_Tag DST_TEST_TAG.C DST_TESTING1.Tag5m.AF
    2019-07-07T12:00:00Z 2.907 1 24 0
    2019-07-07T18:00:00Z 5 0 12 1


    I thought I was onto something using the script below but my rows are being exponentially increased as I expand out the data

    Code:
    let
        Source = Json.Document(File.Contents("C:\Users\rcl\Desktop\JSON Test Data.json")),
        Items = Source[Items],
        #"Converted to Table" = Table.FromList(Items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Name", "Items"}, {"Column1.Name", "Column1.Items"}),
        #"Transposed Table" = Table.Transpose(#"Expanded Column1"),
        #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Testtag", type any}, {"DST_TEST_TAG", type any}, {"DST_TEST_TAG.C", type any}, {"DST_TESTING1.Tag5m.AF", type any}}),
        #"Expanded Testtag" = Table.ExpandListColumn(#"Changed Type", "Testtag"),
        #"Expanded Testtag1" = Table.ExpandRecordColumn(#"Expanded Testtag", "Testtag", {"Timestamp", "Value"}, {"Testtag.Timestamp", "Testtag.Value"}),
        #"Expanded DST_TEST_TAG" = Table.ExpandListColumn(#"Expanded Testtag1", "DST_TEST_TAG"),
        #"Expanded DST_TEST_TAG1" = Table.ExpandRecordColumn(#"Expanded DST_TEST_TAG", "DST_TEST_TAG", {"Value"}, {"DST_TEST_TAG.Value"}),
        #"Expanded DST_TEST_TAG.C" = Table.ExpandListColumn(#"Expanded DST_TEST_TAG1", "DST_TEST_TAG.C"),
        #"Expanded DST_TEST_TAG.C1" = Table.ExpandRecordColumn(#"Expanded DST_TEST_TAG.C", "DST_TEST_TAG.C", {"Value"}, {"DST_TEST_TAG.C.Value"}),
        #"Expanded DST_TESTING1.Tag5m.AF" = Table.ExpandListColumn(#"Expanded DST_TEST_TAG.C1", "DST_TESTING1.Tag5m.AF"),
        #"Expanded DST_TESTING1.Tag5m.AF1" = Table.ExpandRecordColumn(#"Expanded DST_TESTING1.Tag5m.AF", "DST_TESTING1.Tag5m.AF", {"Value"}, {"DST_TESTING1.Tag5m.AF.Value"})
    in
        #"Expanded DST_TESTING1.Tag5m.AF1"
    In this example where there are only 2 data points for each Item, the first and last rows end up with the correct data. However, as more data points are added, this isn't the case. The correct rows end up mixed all over in the data set.
    Click image for larger version. 

Name:	JSON Powerquery Example.jpg 
Views:	16 
Size:	24.0 KB 
ID:	9226

    Any help would be greatly appreciated!
    Thanks, Rhett.
    Attached Files Attached Files

  2. #2
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    26
    Articles
    0
    Excel Version
    Office 365
    Hi Rhett,

    Is this what you were looking for?

    Code:
    let
        Source = Json.Document(File.Contents("C:\T\JSON Test Data.json")),
        Items = Table.FromRecords( Source[Items]),
        #"Expanded Items" = Table.ExpandListColumn(Items, "Items"),
        #"Expanded Items1" = Table.ExpandRecordColumn(#"Expanded Items", "Items", {"Timestamp", "Value"}, {"Timestamp", "Value"}),
        #"Pivoted Column" = Table.Pivot(#"Expanded Items1", List.Distinct(#"Expanded Items1"[Name]), "Name", "Value", List.Sum)
    in
        #"Pivoted Column"

    Regards,
    Mike
    Last edited by AliGW; 2019-07-14 at 08:50 AM. Reason: Code tags added - remember next time, please.

  3. #3
    Neophyte rlenterprises66's Avatar
    Join Date
    Jul 2019
    Posts
    2
    Articles
    0
    Excel Version
    Excel for Office 365
    Quote Originally Posted by cyborgski View Post
    Hi Rhett,

    Is this what you were looking for?

    let
    Source = Json.Document(File.Contents("C:\T\JSON Test Data.json")),
    Items = Table.FromRecords( Source[Items]),
    #"Expanded Items" = Table.ExpandListColumn(Items, "Items"),
    #"Expanded Items1" = Table.ExpandRecordColumn(#"Expanded Items", "Items", {"Timestamp", "Value"}, {"Timestamp", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Items1", List.Distinct(#"Expanded Items1"[Name]), "Name", "Value", List.Sum)
    in
    #"Pivoted Column"


    Regards,
    Mike

    YES!!! That was exactly what I was looking for! The Table.FromRecords() function was what I needed!

    Thanks so much!

  4. #4
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    26
    Articles
    0
    Excel Version
    Office 365
    Hi Rhett,

    For educational purposes, I would like to point out that even Table.FromList works. You just needed two more expands and then pivot instead of transpose to get the same result.

    Code:
    let
        Source = Json.Document(File.Contents("C:\T\JSON Test Data.json")),
       Items = Table.FromList(Source[Items], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(Items , "Column1", {"Name", "Items"}, {"Column1.Name", "Column1.Items"}),
        #"Expanded Column1.Items" = Table.ExpandListColumn(#"Expanded Column1", "Column1.Items"),
        #"Expanded Column1.Items1" = Table.ExpandRecordColumn(#"Expanded Column1.Items", "Column1.Items", {"Timestamp", "Value"}, {"Timestamp", "Value"}),
        #"Pivoted Column" = Table.Pivot(#"Expanded Column1.Items1", List.Distinct(#"Expanded Column1.Items1"[Column1.Name]), "Column1.Name", "Value", List.Sum)
    in
        #"Pivoted Column"
    
    
    
    Regards,
    Mike
    Last edited by AliGW; 2019-07-14 at 08:49 AM. Reason: Code tags added

  5. #5
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,235
    Articles
    0
    Excel Version
    Office 365 Subscription
    Mike - FYI we expect code tags to be used around code snippets, please (VBA or M Code). Thanks.
    Ali
    Enthusiastic self-taught user of MS Excel!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •