How to pass the request object as query string in Power Query Formula Language

Peddabomma

Could you share how did you manage to make the API work? What was wrong?

To convert the json to a table, I think you can reference the power query below. I am using a JSON file other than web API, however the transformations are in the same way, you can just replace the source accordingly.

let
    Source = Json.Document(File.Contents("C:\test\json.json")),
    rows = Source[rows],
    #"Converted to Table" = Table.FromList(rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Added Index", "Column1", {"values"}, {"Column1.values"}),
    #"Expanded Column1.values" = Table.ExpandListColumn(#"Expanded Column1", "Column1.values"),
    #"Added Column" = Table.AddColumn(#"Expanded Column1.values", "isValue", each
      let
        result = try Number.From([Column1.values]) otherwise "Not value",
        resultType = if result = "Not value" then "N" else "Y"
      in
        resultType
    ),
    #"Filtered Rows" = Table.SelectRows(#"Added Column", each ([isValue] = "N")),
    #"Expanded Column1.values1" = Table.ExpandListColumn(#"Filtered Rows", "Column1.values"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Column1.values1",{"Index"},#"Added Column",{"Index"},"NewColumn",JoinKind.Inner),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Column1.values", "isValue"}, {"NewColumn.Column1.values", "NewColumn.isValue"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded NewColumn", each ([NewColumn.isValue] = "Y")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Index", "isValue", "NewColumn.isValue"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.values", "ms_prod"}, {"NewColumn.Column1.values", "measure_value"}})
    
in
    #"Renamed Columns"