none
Connecting to dynamic JSON object - when 1 row no array, but multiple rows then data in an array RRS feed

  • Question

  • Hi

    How do I deal with this scenario in PowerQuery? 

    When I only have 1 record my dataset looks like this:

    With the Advanced Editor looking like this:

    let
        Source = Json.Document(Web.Contents("http://localhost:58025/api/JSONFeed")),
        gvdata = Source[gvdata],
        list = gvdata[list],
        record = list[record],
        #"Converted to Table" = Record.ToTable(record),
        #"Transposed Table" = Table.Transpose(#"Converted to Table"),
        #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"@id", type text}, {"@type", type text}, {"@op_id", type text}, {"@summary", type text}, {"@op_num_3", type number}, {"@op_num_24", type number}, {"@op_ud4_numeric", type number}})
    in
        #"Changed Type"

    However when I have more than one record, then they appear in an array. Like this:


    When in the Advanced Editor looks like this:

    let
        Source = Json.Document(Web.Contents("http://localhost:58025/api/reports/ConfirmedBusinessWeekly")),
        gvdata = Source[gvdata],
        list = gvdata[list],
        record = list[record],
        #"Converted to Table" = Table.FromList(record, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"@id", "@type", "@op_id", "@summary", "@op_num_3", "@op_num_24", "@op_ud4_numeric", "@op_num_4", "@op_ud9_date", "@op_ud9_id_name"}, {"@id", "@type", "@op_id", "@summary", @op_num_3", "@op_num_24", "@op_ud4_numeric", "@op_num_4", "@op_ud9_date", "@op_ud9_id_name"})
    in
        #"Expanded Column1"

    How do I manage both scenarios within the one query or am I going about it the wrong way? 


    Monday, July 16, 2018 7:57 PM

Answers

All replies

  • Hi there. You can add M code like the following to have special logic for the record vs. list of records cases:

    if value is list then ... else ...

    Does that help?

    Ehren

    Wednesday, July 18, 2018 9:07 PM
    Owner
  • Hi there. You can add M code like the following to have special logic for the record vs. list of records cases:

    if value is list then ... else ...

    Does that help?

    Ehren

    Hi Ehren,

    I looked into suggesting something similar but it won't work unless the source is specified using a parameter. As is, the queries are independent, since each point to a different source.

    Thursday, July 19, 2018 5:52 PM
  • Adding to Ehrens suggestion: The function to check for the type is Value.Is

    (Assuming that the different sources are just a matter of demo here)


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, July 29, 2018 8:19 AM
    Moderator