Looping throu REST-API to get all JSON content / Error during saving in PowerQuery

[jofujofu]

Hi,

I am searching for a simple solution to loop throu a website-content API to get all Records...

Fond a simple way by the use of a function: 

(Skip_It as number) as table => 
let
Quelle1 = Web.Contents("https://URL-DELETED?skip=" & Number.ToText(Skip_It)),
Quelle = Json.Document(Quelle1),
data = Quelle[data],
#"In Tabelle konvertiert" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Erweiterte Column1" = Table.ExpandRecordColumn(#"In Tabelle konvertiert", "Column1", {"_id", "_type", "city", "email", "geoLat", "geoLng", "isXSLocation", "mainCategoryUid", "name", "phone", "postalCode", "street", "uid", "website"}, {"Column1._id", "Column1._type", "Column1.city", "Column1.email", "Column1.geoLat", "Column1.geoLng", "Column1.isXSLocation", "Column1.mainCategoryUid", "Column1.name", "Column1.phone", "Column1.postalCode", "Column1.street", "Column1.uid", "Column1.website"})
in
#"Erweiterte Column1"

 This funtion works well with a table by skipping record-numbers für the API Call

BUT: when i want do close/save the PowerQuery Editor i get an strange error:

"Der Ausdruck in der Partition "n/a" in Tabelle "n/a" verweist auf eine unbekannte Entität."

meaning there is a partition in an unknown table for an unknown entity???

What did i do wrong in den funtion? Did not found anything wrong here, no syntax error....???

Thanks for a hint!

Josef

[ImkeF]

Hi @jofujofu  

that looks really strange. Hard to see that the function is actually the reason for it. 

Could it be a problem in the query instead that you're calling the function from?

[jofujofu]

Hi Imke,

the query deliveres the rights results. Also the function workes fine, when Triggered manually (step by step codeing in the enhenced editor in PowerBI... After Saveing I always receives this Error (didnt found the english equivalent!):

Zurückgegebener Fehler: "Der Ausdruck in der Partition "n/a" in Tabelle "n/a" verweist auf eine unbekannte Entität. ".

Must have to do with the transformation from the API Call (Website-API / no uid/pw) to the JSON and furthermore to the Table. It's a simple Web Call....

???

Any hints?

Josef

[ImkeF]

Hi @jofujofu 

do you have the "Store datasets in enhanced metadata format" turned on?

I've had the same error when copy pasting a working set of queries into a new file with the preview feature "Store datasets in enhanced metadata format" turned on. It seems to be a bug that others are experiencing as well, unfortunately:

https://community.powerbi.com/t5/Issues/Error-on-apply-Power-Query-Expression-in-partition-n-a-in-ta...

[jofujofu · Accepted solution]

Hi, @ImkeF  - thanks for the link!

TURNING the "enhanced metadata format" OFF and restart PowerBI WITH an NEW file (ans copyying evry formular via windows editor solved this problem!

Thanks Josef

[Greg_Deckler]

@ImkeF