Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!
Im trying to use Power BI to use an open API from out national Statistics Service in Sweden, but I only get syntax errors with the genereated JSON code from the website. Here is the code I use in the Advanced Editor. Can anyone point me in the right direction?
let Source = "http://api.scb.se/OV0104/v1/doris/sv/ssd/START/BE/BE0101/BE0101A/BefolkningNy" in Source = { "query": [ { "code": "Region", "selection": { "filter": "vs:RegionLän07", "values": [ "10" ] } }, { "code": "Alder", "selection": { "filter": "vs:Ålder1årA", "values": [ "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61", "62", "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75", "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94", "95", "96", "97", "98", "99", "100+" ] } }, { "code": "ContentsCode", "selection": { "filter": "item", "values": [ "BE0101N1" ] } }, { "code": "Tid", "selection": { "filter": "item", "values": [ "2017" ] } } ], "response": { "format": "px" } }
Solved! Go to Solution.
I don't think that you can use the query part as shown above as the query code is invaild Power Query code. You can contact the product's support to check if it is possible to add filters in the URL.
Regards,
Lydia
@Katalo,
Please add a blank query in Power BI Desktop, then paste the following code into Advanced Editor of the blank query. Then you can filter the required code in this query.
let Source = Json.Document(Web.Contents("http://api.scb.se/OV0104/v1/doris/sv/ssd/START/BE/BE0101/BE0101A/BefolkningNy")), #"Converted to Table" = Record.ToTable(Source), Value = #"Converted to Table"{1}[Value], #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"code", "text", "values", "valueTexts", "elimination"}, {"Column1.code", "Column1.text", "Column1.values", "Column1.valueTexts", "Column1.elimination"}), #"Expanded Column1.values" = Table.ExpandListColumn(#"Expanded Column1", "Column1.values"), #"Expanded Column1.valueTexts" = Table.ExpandListColumn(#"Expanded Column1.values", "Column1.valueTexts") in #"Expanded Column1.valueTexts"
Also you can add a custom column to flag the rows you need, then filter all the required rows . If you have any questions about the custom column, please share your logic here so that we can provide you appropriate formula.
Regards,
Lydia
Hello Lydia, and thank you for your response!
I have tried using your solution, however does that give me the expected result. I think I need to use the "query"-code to get the correct data from the API. The result Im looking for is a table with ages 0-100+ (Alder=Age). But when I use the "query" part of the code it gives me syntax errors.
I don't think that you can use the query part as shown above as the query code is invaild Power Query code. You can contact the product's support to check if it is possible to add filters in the URL.
Regards,
Lydia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |