英文:
Import CSV for every row in a dataset in Power BI Desktop
问题
我有一个包含地址的数据集。对于每个地址，我都会向地理编码服务发出网络请求。该服务的响应是一个包含一行数据的CSV文件。我该如何将CSV中的数据添加到我的原始表格中，并对每个地址都重复这个过程？
这是我的Power BI查询（包括创建包含地址数据集的部分）：
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LY9ba4NAEIX/yuCzD3G9YB6VQKRNoFB6QcnD6E50m81YZldK/311m8c53+E7TNdFrx6ekJ3zgughi+EoMxseiaNL3EXlPkmeW8hVuD7IeZJeDN8giWGf7XII4LR4T9yTjKsIFLQ/s7UU0Bnl5iFJoBKe6B6yd+NQiPUmKVWqXho4kf11HnXglQyTuZMmZxEZihjSvMyg/oQ3LzRMPrQOQnSFFBpEsQ9zuxgNzTqOrL8tGd4mDsRbaQyNWrBH9kH8YPXshinAZAeFUns4Vv9/Vb02X1DA2WhNtl9kdVz+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [adres = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"adres", type text}}),
#"Build URL" = Table.AddColumn(#"Changed Type", "URL", each "https://geodata.nationaalgeoregister.nl/locatieserver/v3/free?fq=type:adres&fl=*&rows=1&wt=csv&q=" & [adres]),
#"Get Attributes" = Table.AddColumn(#"Build URL", "attrs", each Web.Contents([URL]))
in
#"Get Attributes"
我已成功切换到使用Web服务的JSON响应进行工作，您可以在此链接中找到示例：
https://gist.githubusercontent.com/FrieseWoudloper/68de3449287c9ed43ac4f140d3991e4c/raw/9be21d051e2645ee581d1b9567410b2e49b3c4b5/provinciehuis.txt
不过，出于学习目的，我也很想知道如何处理CSV响应的解决方案。
英文:
I have a dataset with addresses. For each adress I do a web request to a geocoding service. The response of the service is a CSV-file with one row. How do I add the data in the CSV to my original table, and repeat this for every address?
This is my Power BI query (including creating the dataset with addresses):
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LY9ba4NAEIX/yuCzD3G9YB6VQKRNoFB6QcnD6E50m81YZldK/311m8c53+E7TNdFrx6ekJ3zgughi+EoMxseiaNL3EXlPkmeW8hVuD7IeZJeDN8giWGf7XII4LR4T9yTjKsIFLQ/s7UU0Bnl5iFJoBKe6B6yd+NQiPUmKVWqXho4kf11HnXglQyTuZMmZxEZihjSvMyg/oQ3LzRMPrQOQnSFFBpEsQ9zuxgNzTqOrL8tGd4mDsRbaQyNWrBH9kH8YPXshinAZAeFUns4Vv9/Vb02X1DA2WhNtl9kdVz+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [adres = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"adres", type text}}),
#"Build URL" = Table.AddColumn(#"Changed Type", "URL", each "https://geodata.nationaalgeoregister.nl/locatieserver/v3/free?fq=type:adres&fl=*&rows=1&wt=csv&q=" & [adres]),
#"Get Attributes" = Table.AddColumn(#"Build URL", "attrs", each Web.Contents([URL]))
in
#"Get Attributes"
I managed to get it working switching to JSON response of the web service:
https://gist.githubusercontent.com/FrieseWoudloper/68de3449287c9ed43ac4f140d3991e4c/raw/9be21d051e2645ee581d1b9567410b2e49b3c4b5/provinciehuis.txt
However I am curious to know the solution with the CSV response, also for learning purposes.
答案1
得分: 1
我添加了一个自定义列，将二进制转换为具有提升标题的表格，然后您可以随意访问所有列
我得到了将二进制转换的代码，通过钻取其中一个，它自动创建了我在自定义列上使用的 Csv.Document 语法。
英文:
I added custom column that converts binary to table with promoted headers, then you can access all the columns as you please
I got the code converting the binary by drilling into one of them it automatically crated the Csv.Document syntax that I used on the custom column
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LY9ba4NAEIX/yuCzD3G9YB6VQKRNoFB6QcnD6E50m81YZldK/311m8c53+E7TNdFrx6ekJ3zgughi+EoMxseiaNL3EXlPkmeW8hVuD7IeZJeDN8giWGf7XII4LR4T9yTjKsIFLQ/s7UU0Bnl5iFJoBKe6B6yd+NQiPUmKVWqXho4kf11HnXglQyTuZMmZxEZihjSvMyg/oQ3LzRMPrQOQnSFFBpEsQ9zuxgNzTqOrL8tGd4mDsRbaQyNWrBH9kH8YPXshinAZAeFUns4Vv9/Vb02X1DA2WhNtl9kdVz+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [adres = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"adres", type text}}),
#"Build URL" = Table.AddColumn(#"Changed Type", "URL", each "https://geodata.nationaalgeoregister.nl/locatieserver/v3/free?fq=type:adres&fl=*&rows=1&wt=csv&q=" & [adres]),
#"Get Attributes" = Table.AddColumn(#"Build URL", "attrs", each Web.Contents([URL])),
#"Added Custom" = Table.AddColumn(#"Get Attributes", "Custom", each Table.PromoteHeaders(Csv.Document([attrs],[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]))),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"attrs"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"bron", "woonplaatscode", "type", "woonplaatsnaam", "wijkcode", "huis_nlt", "openbareruimtetype", "buurtnaam", "gemeentecode", "rdf_seealso", "weergavenaam", "suggest", "straatnaam_verkort", "id", "gekoppeld_perceel", "gemeentenaam", "buurtcode", "wijknaam", "identificatie", "openbareruimte_id", "waterschapsnaam", "provinciecode", "postcode", "provincienaam", "centroide_ll", "geometrie_ll", "nummeraanduiding_id", "waterschapscode", "adresseerbaarobject_id", "huisnummer", "provincieafkorting", "geometrie_rd", "centroide_rd", "straatnaam"}, {"Custom.bron", "Custom.woonplaatscode", "Custom.type", "Custom.woonplaatsnaam", "Custom.wijkcode", "Custom.huis_nlt", "Custom.openbareruimtetype", "Custom.buurtnaam", "Custom.gemeentecode", "Custom.rdf_seealso", "Custom.weergavenaam", "Custom.suggest", "Custom.straatnaam_verkort", "Custom.id", "Custom.gekoppeld_perceel", "Custom.gemeentenaam", "Custom.buurtcode", "Custom.wijknaam", "Custom.identificatie", "Custom.openbareruimte_id", "Custom.waterschapsnaam", "Custom.provinciecode", "Custom.postcode", "Custom.provincienaam", "Custom.centroide_ll", "Custom.geometrie_ll", "Custom.nummeraanduiding_id", "Custom.waterschapscode", "Custom.adresseerbaarobject_id", "Custom.huisnummer", "Custom.provincieafkorting", "Custom.geometrie_rd", "Custom.centroide_rd", "Custom.straatnaam"})
in
#"Expanded Custom"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论