Need help with transforming curl into power BI

[Stepo]

can please someone help me convert following curl request into power query

thanks

curl -X 'POST' \  'https://www-genesis.destatis.de/genesisWS/rest/2020/data/tablefile' \  -H 'accept: application/octet-stream' \  -H 'username: c6657d00306f4edf91f2cefe54ae5640' \  -H 'password: GAST' \  -H 'Content-Type: application/x-www-form-urlencoded' \  -d 'compress=false&name=61111-0002&area=free&stand=01.01.1970%2001%3A00&format=datencsv&language=de&transpose=false&job=false&quality=off'

[V-yubandi-msft]

Hi @Stepo ,

Thank you for reaching out to the Fabric Community. The M code provided by our @kushanNa  appears correct and should help you replicate your cURL request in Power Query. Please follow the steps they shared.

If you encounter authentication issues such as 401 or 403 errors, it may be due to how the API manages credentials. In that case, we can try using Basic Authentication instead of passing the username and password in the headers.

Helpfu Reference:

Text.FromBinary - PowerQuery M | Microsoft Learn
Web.Contents - PowerQuery M | Microsoft Learn
Csv.Document - PowerQuery M | Microsoft Learn

Thank you for your response, @kushanNa 

Best regards,
Yugandhar

[Stepo]

Dear Jugandhar,

thanks for your reply.

There is no issue with authentification, this works, but with how to transform the data from the received json to csv.

[kushanNa · Accepted solution]

Hi @Stepo 

Can you try out this code? 

let
    url = "https://www-genesis.destatis.de/genesisWS/rest/2020/data/tablefile",

    // Request body (x-www-form-urlencoded)
    body = "regionalkey=&compress=false&name=&area=free&timeslices=&classifyingkey1=&classifyingkey2=&classifyingkey3=&classifyingkey4=&classifyingkey5=&stand=01.01.1970%2001%3A00&classifyingvariable1=&format=datencsv&classifyingvariable2=&language=de&endyear=&classifyingvariable3=&transpose=false&classifyingvariable4=&contents=&classifyingvariable5=&regionalvariable=&job=false&quality=off&startyear=",

    // POST request
    Source = Web.Contents(
        url,
        [
            Headers = [
                #"accept" = "application/octet-stream",
                #"username" = "xxxxxxxxx",
                #"password" = "GAST",
                #"Content-Type" = "application/x-www-form-urlencoded"
            ],
            Content = Text.ToBinary(body)
        ]
    ),

    // Convert binary response to text
    ResultText = Text.FromBinary(Source, TextEncoding.Utf8),

    // Parse CSV directly (since API returns CSV)
    CsvParsed = Csv.Document(ResultText, [Delimiter=";", Encoding=65001, QuoteStyle=QuoteStyle.None])
in
    CsvParsed

Works for me 

if it's not working for you try to generate a new token and see if it works ? 

[Stepo]

HI kushanNa,

Got the same result as you. But this data is incomplete.

But I managed to adapt your code and solved my issue. The API returns application/zip. So I added a code to unzip the file. 

But thanks a lot for your support!!

[Stepo]

[Stepo]

Thanks a lot for your fast response.

I tried your code and sucessfully received a Json document as Source.

But win the Text. part I get an error. Can you identify the mistake? I know that the document provided in the end should be a zip file. By the way there is no nedd of adding extra password, it is done via a token in the username. So also you should get the data without extra credentialx. Data is public available and not confidential.

[kushanNa]

Hi @Stepo 

please try out the following M code in Power Query and see if it works for you ? 

Get Data > Blank Query > Advanced Editor > Copy Past the Code > Done> Edit Credentials > Connect

let
    url = "https://www-genesis.destatis.de/genesisWS/rest/2020/data/tablefile",
    body = "compress=false&name=61111-0002&area=free&stand=01.01.1970%2001%3A00&format=datencsv&language=de&transpose=false&job=false&quality=off",
    Source = Web.Contents(
        url,
        [
            Content = Text.ToBinary(body),
            Headers = [
                #"accept" = "application/octet-stream",
                #"username" = "c6657d00306f4edf91f2cefe54ae5640",
                #"password" = "GAST",
                #"Content-Type" = "application/x-www-form-urlencoded"
            ],
            ManualStatusHandling={400, 404, 500}
        ]
    ),
    // The API returns CSV in octet-stream, so parse it
    ToText = Text.FromBinary(Source, TextEncoding.Utf8),
    Csv = Csv.Document(ToText, [Delimiter=";", Encoding=65001, QuoteStyle=QuoteStyle.Csv])
in
    Csv

[Stepo]

HI,

thank you so much for your fast reply.

I tried your code and received the json document as Source

But I got an error when doing the Text. part

Can you identify the mistake? By the way the data is not confidential (public available) and there is no need of typing a password, it is done via a token in the username. So also you should be able to get the data  with the above mentioned username.

Thanks a lot