Forum Discussion
Missing column after expanding column from nested json file
Hi all,
Perhaps a stupid question; but I'm getting an error when loading the changes I've made in the Power Query Editor saying it cannot find column X from the table.
This column X is one of the columns that I've expanded in earlier steps in the Power Query Editor, to get the direct right from a nested Json file I'm connecting with.
Anyone an idea what's is going wrong here?
Kind regards,
Jorn Berends
5 Replies
Can you post your M Code, and possibly a sample of the JSON data? Very hard for us to diagnose "It's not working" when we don't know what you did.
sorry, new here 🙂 here's the code, unfortunately it is in Dutch. If needed, I can share an English version as well. The column that's missing when loading the data into the report is 'uniqueIdField'
let
Bron = Json.Document(Web.Contents("https://services2.arcgis.com/F10cLdaDFADEoMkn/arcgis/rest/services/KRW_oppervlaktewateren_oordelen_2021_2023/FeatureServer/13/query?where=1%3D1&outFields=*&returnGeometry=false&resultRecordCount=0&f=json&token=3NKHt6i2urmWtqOuugvr9c3BFphpnZruNdo-dsIfaklIH1KtzcErb-slWgD6yZYI2s2bBMfcwHFpUuYifGG1OFCA34C0nMY10-NDRWDnhRvMZcu-c0vqZnf3TF9e1DmNUAg1YJY4qwYAEToibZmh1jVOGmrlCAs11PspYA51c9zH2pT1Q8HSJceQs2zJ7nqJCUnsY8Rn4uAlnNceJLexoOGC0Xo-SdDAAoST_g0zA9YDTT8cxR5-Ywg5NMzy9Q256HQZSa4237BUsKqRTQ..")),
#"Geconverteerd naar tabel" = Table.FromRecords({Bron}),
#"uniqueIdField uitgevouwen" = Table.ExpandRecordColumn(#"Geconverteerd naar tabel", "uniqueIdField", {"name", "isSystemMaintained"}, {"uniqueIdField.name", "uniqueIdField.isSystemMaintained"}),
#"geometryProperties uitgevouwen" = Table.ExpandRecordColumn(#"uniqueIdField uitgevouwen", "geometryProperties", {"shapeAreaFieldName", "shapeLengthFieldName", "units"}, {"geometryProperties.shapeAreaFieldName", "geometryProperties.shapeLengthFieldName", "geometryProperties.units"}),
#"spatialReference uitgevouwen" = Table.ExpandRecordColumn(#"geometryProperties uitgevouwen", "spatialReference", {"wkid", "latestWkid"}, {"spatialReference.wkid", "spatialReference.latestWkid"}),
#"Type gewijzigd" = Table.TransformColumnTypes(#"spatialReference uitgevouwen",{{"objectIdFieldName", type text}, {"uniqueIdField.name", type text}, {"uniqueIdField.isSystemMaintained", type logical}, {"globalIdFieldName", type text}, {"geometryProperties.shapeAreaFieldName", type text}, {"geometryProperties.shapeLengthFieldName", type text}, {"geometryProperties.units", type text}, {"geometryType", type text}, {"spatialReference.wkid", Int64.Type}, {"spatialReference.latestWkid", Int64.Type}, {"fields", type any}, {"features", type any}}),
features = #"Type gewijzigd"{0}[features],
#"Geconverteerd naar tabel1" = Table.FromList(features, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Column1 uitgevouwen" = Table.ExpandRecordColumn(#"Geconverteerd naar tabel1", "Column1", {"attributes"}, {"Column1.attributes"}),
#"Column1.attributes uitgevouwen" = Table.ExpandRecordColumn(#"Column1 uitgevouwen", "Column1.attributes", {"OBJECTID", "db_id", "owmident", "namespac", "owmcons", "owmnaam", "sgbident", "owmstat", "oppvte", "lengte", "owmtype", "owmtyped", "schaal", "geldig_b", "geldig_e", "gtype", "wkrv_id", "Waterlichaam_identificatie", "Stroomgebieddistrict", "Waterlichaam_Versie", "Rapportagejaar", "Tpering_Code", "Typering_Omschrijving", "Grootheid_Code", "Grootheid_Omschrijving", "ChemischeStof_code", "ChemischeStof_Omschrijving", "Hoedanigheid_Code", "Hoedanigheid_Omschrijving", "Oordeel", "Oordeel_Type_Omschrijving", "WaardebepalingsMethode_Code", "WaardebepalingsMethode_Omschrij", "description", "Stroomgebieddistrict_Code", "Deelstroomgebieddistrict_Code", "Typering_Code", "Oordeelsoort_Code", "Motivering_wijziging_toestand", "Shape__Area", "Shape__Length"}, {"OBJECTID", "db_id", "owmident", "namespac", "owmcons", "owmnaam", "sgbident", "owmstat", "oppvte", "lengte", "owmtype", "owmtyped", "schaal", "geldig_b", "geldig_e", "gtype", "wkrv_id", "Waterlichaam_identificatie", "Stroomgebieddistrict", "Waterlichaam_Versie", "Rapportagejaar", "Tpering_Code", "Typering_Omschrijving", "Grootheid_Code", "Grootheid_Omschrijving", "ChemischeStof_code", "ChemischeStof_Omschrijving", "Hoedanigheid_Code", "Hoedanigheid_Omschrijving", "Oordeel", "Oordeel_Type_Omschrijving", "WaardebepalingsMethode_Code", "WaardebepalingsMethode_Omschrij", "description", "Stroomgebieddistrict_Code", "Deelstroomgebieddistrict_Code", "Typering_Code", "Oordeelsoort_Code", "Motivering_wijziging_toestand", "Shape__Area", "Shape__Length"})
in
#"Column1.attributes uitgevouwen"Hi,
Thanks for the solutions PwerQueryKees , watkinnc and ToddChitt offered and i want to offer some more information for user to refer to.
hello JornBerends , based on the code you have offerd, you uniqueIdField looks like a table, can you provide some pictures of the step that you expand this column?
Best Regards!
Yolo Zhu
Table.ExpandRecordColumn removes the field you are expanding....
