none
Nested Json via API and combining lists RRS feed

  • Question

  • Hi,

    I`m having an issue that I can't quite figure out. I`m relatively new to power query in Power BI. This is the code I have.

    let
    	Url = "https://xx.com/magento-api/rest/customers?order=entity_id&dir=asc",
    	Limit = "100",
    	consumerKey = "xx",
    	Token = "xx",
    	SignatureMethod = "PLAINTEXT",
    	Signature = "xx",
    	TimeStamp = "1551909334",
    	Nonce = "2NuM9DBGZHb",
        
    
            Pagination = List.Skip(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value      
       	
            each [Counter]<5,
            each [ WebCall = Function.InvokeAfter(
                ()=>Json.Document(Web.Contents(Url, 
                    [Query=[limit="" & Limit & "",page="" & Text.From([Page]) & ""],
                    Headers=[Authorization="OAuth 
                        oauth_consumer_key=" & consumerKey & ", 
                        oauth_token=" & Token & ", 
                        oauth_signature_method=" & SignatureMethod  & ", 
                        oauth_timestamp=" & TimeStamp  & ", 
                        oauth_nonce=" & Nonce & ", 
                        oauth_version=""1.0"", 
                        oauth_signature=" & Signature & "
                    "]
                ])), 
                #duration(0,0,0,1)),          
                Page = [Page]+1,
                Counter = [Counter]+1  
    	]
    	) ,1),
        
            Table= Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            column= Table.ExpandRecordColumn(Table, "Column1", {"WebCall"}, {"Column1.WebCall"}),
            newList = column[Column1.WebCall],
    
    	combinedList= List.Generate(()=>
    		[ 
    			Counter2 = 0, 
    			combinedList = newList{0}
    		], //initial
    
    		each [Counter2] < List.Count(newList), //condition
    
    		each [
    			combinedList = [combinedList] & newList{Counter2},
    			Counter2 = [Counter2]+1
    		]
    	)
    
    
    in
        combinedList


    The <g class="gr_ gr_247 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling ins-del multiReplace" data-gr-id="247" id="247">Json</g> result by the API looks like this

    {
      "2": {
        "entity_id": "2",
        "website_id": "1",
        "email": "test@example.com",
        "group_id": "1",
        "created_at": "2012-03-22 14:15:54",
        "disable_auto_group_change": "1",
        "firstname": "john",
        "lastname": "Doe",
        "created_in": "Admin",
        "prefix": null,
        "suffix": null,
        "taxvat": null,
        "dob": "2001-01-03 00:00:00",
        "reward_update_notification": "1",
        "reward_warning_notification": "1",
        "gender": "1"
      },
      "4": {
        "entity_id": "4",
        "website_id": "1",
        "email": "earl@example.com",
        "group_id": "1",
        "created_at": "2013-03-28 18:59:41",
        "disable_auto_group_change": "0",
        "firstname": "Earl",
        "lastname": "Hickey",
        "created_in": "Admin",
        "prefix": null,
        "suffix": null,
        "taxvat": null,
        "dob": "2012-03-28 13:54:04",
        "reward_update_notification": "1",
        "reward_warning_notification": "1",
        "gender": "1"
      }


    When I use the following instead of the generate<g class="gr_ gr_323 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style replaceWithoutSep" data-gr-id="323" id="323">.list</g> loop I get the results I want: 

     combinedList= newList{0} & newList{1} & newList{2} & newList{3}

    Where each number would represent a page. But <g class="gr_ gr_387 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Punctuation only-ins replaceWithoutSep" data-gr-id="387" id="387">obviously</g> I cannot use that as the results from the API will have 100s of pages so I have to count the list results from the pagination and go through them. The problem is that I get a different result when using the list<g class="gr_ gr_948 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style replaceWithoutSep" data-gr-id="948" id="948">.generate</g> code above then when I concatenate the results manually. So my question is, how can I adapt my code to the result I need?

    BTW this is the result I get when using the list<g class="gr_ gr_1777 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style replaceWithoutSep" data-gr-id="1777" id="1777">.generate</g> function. Which is basically the same as at the step before.

    LIST

    1. Record

    2. Record

    3. Record

    4.Record

    The problem is that when I just extend the records at step 'table' the index numbers are showing up as columns instead of rows. 

     This is the result I get when concatenating manually using &.

    1. Record

    2. Record

    3. Record

    4. Record

    5. Record

    etc.. a record for each item in the <g class="gr_ gr_2915 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="2915" id="2915">json</g> request.

    When I extend the record here I get the proper columns and each index number is a row.

    Any ideas?

    Tuesday, March 12, 2019 11:19 PM

Answers

  • Ok, I see.

    let
    // simulate a table of records:
        column = #table(
            {"Column1.WebCall"},
            {
                { [1=[a=1,b=2,c=3], 2=[a=4,b=5,c=6], 3=[a=7,b=8,d=9]] },
                { [4=[a=10,b=20,c=30], 5=[a=40,b=50,c=60], 6=[a=70,b=80,d=90]] }
            }
            ),
    // convert EACH record to the table of columns Name and Value^
        RecordsToTables = Table.TransformColumns(
            column, 
            {"Column1.WebCall", Record.ToTable}
            ),
    // expand these tables
        ExpandTables = Table.ExpandTableColumn(RecordsToTables, "Column1.WebCall", {"Name", "Value"}, {"WebCall.Id", "Value"}),
    // get all field names of all inner records of Value column:
        Recs = List.Buffer(ExpandTables[Value]), 
        FieldNames = List.Union(List.Transform(Recs, Record.FieldNames)),
    // expand these records with the full list of field names:
        ExpandRecords = Table.ExpandRecordColumn(ExpandTables, "Value", FieldNames, FieldNames)
    in
        ExpandRecords

    On a first ('column') step I simulate your last screenshot - a table with one column, which contains records.

    Then a few conversions which is applicable to your code


    Maxim Zelensky Excel Inside

    • Marked as answer by aukevv Thursday, March 14, 2019 1:23 PM
    Thursday, March 14, 2019 9:57 AM

All replies

  • Hi.

    If I get your question correctly, 

    try this:

    // above is the result of your code up to 'column' step
    
        GetValuesOnly = Table.TransformColumns(column, {"Column1.WebCall", each Record.FieldValues(_){0}}),
        ResultTable = Table.FromRecords(GetValuesOnly[#"Column1.WebCall"])
    in
        ResultTable

    As far as I can see, entity_id is the same as the record field names, i.e. initial record field "2" has the record value with the field "entity_id"=2


    Maxim Zelensky Excel Inside

    Wednesday, March 13, 2019 7:05 AM
  • Maxim, thank you for your reply. I tried this but instead of the expected 400 results I get 4. It seems only the first row of each page. So with ids 1, 200, 300 etc.

    It also seems that not all results have the same fields. So when the second result doesn't have a field that the first one did have I get an error like this:

    Expression.Error: The field taxvat of the record wasn't found.
    Details:
        entity_id=107
        website_id=1

    • Edited by aukevv Wednesday, March 13, 2019 2:37 PM
    Wednesday, March 13, 2019 2:35 PM
  • I tried this but instead of the expected 400 results I get 4. It seems only the first row of each page. So with ids 1, 200, 300 etc.

    Ok, lets check. On the Pagination step you should get a list of records. Each record has

    • WebCall field, where the data is stored. As far as I can see each WebCall should contain a list of up to 100 json responce records.
    • Page field
    • Counter field

    Then you create a table from this list of records. So on the step 'column' you should get a list of 4 (?) lists (WebCalls), each is a list of up to 100 json records.

    Is it so?


    Maxim Zelensky Excel Inside

    Wednesday, March 13, 2019 5:00 PM
  • When I click I see a Webcall field with a record Record and a Page and Counter field. When I create a table I have a column with 4 records.

    When I click on those records though I see again a webcall field with a record, a page, and a counter field. Clicking on that record shows me 100 JSON results.




    • Edited by aukevv Wednesday, March 13, 2019 5:19 PM
    Wednesday, March 13, 2019 5:18 PM
  • It is hard to recreate all this scene in imagination only. Can you attach a screenshot with the results of 'column' step (so we can see what lies inside each of 4 records - if you click to the right of the word Record we can see a record structure at the bottom of screen)

    Maxim Zelensky Excel Inside

    Wednesday, March 13, 2019 6:06 PM
  • Sure here are the results of the column step:

    My account isn't verified yet so I have to do it this way:

    URL: awesomescreenshot[dot]com/image/3915903/93b71cb0fd98efc7c4500eade1923580



    • Edited by aukevv Wednesday, March 13, 2019 7:02 PM
    Wednesday, March 13, 2019 7:01 PM
  • Then if I would extend the column and select WebCalls, I get this

    awesomescreenshot[dot]com/image/3915914/1cef7bd732366c18215ce578d94a7f5b

    As you can see the columns are the id's of the results in the JSON request whereas I would need them to be the variables inside these ids. So entity_name etc..

    Wednesday, March 13, 2019 7:07 PM
  • Ok, I see.

    let
    // simulate a table of records:
        column = #table(
            {"Column1.WebCall"},
            {
                { [1=[a=1,b=2,c=3], 2=[a=4,b=5,c=6], 3=[a=7,b=8,d=9]] },
                { [4=[a=10,b=20,c=30], 5=[a=40,b=50,c=60], 6=[a=70,b=80,d=90]] }
            }
            ),
    // convert EACH record to the table of columns Name and Value^
        RecordsToTables = Table.TransformColumns(
            column, 
            {"Column1.WebCall", Record.ToTable}
            ),
    // expand these tables
        ExpandTables = Table.ExpandTableColumn(RecordsToTables, "Column1.WebCall", {"Name", "Value"}, {"WebCall.Id", "Value"}),
    // get all field names of all inner records of Value column:
        Recs = List.Buffer(ExpandTables[Value]), 
        FieldNames = List.Union(List.Transform(Recs, Record.FieldNames)),
    // expand these records with the full list of field names:
        ExpandRecords = Table.ExpandRecordColumn(ExpandTables, "Value", FieldNames, FieldNames)
    in
        ExpandRecords

    On a first ('column') step I simulate your last screenshot - a table with one column, which contains records.

    Then a few conversions which is applicable to your code


    Maxim Zelensky Excel Inside

    • Marked as answer by aukevv Thursday, March 14, 2019 1:23 PM
    Thursday, March 14, 2019 9:57 AM
  • Maxim, thanks. I quickly tried this before my morning coffee but it seemed to work. Will look at it closely later today. Also gave me some new functions to explore and understand. Thanks a lot!

    Thursday, March 14, 2019 1:23 PM