From table to JSON with Power BI / Power Query

0
votes

I need to convert the following table:

enter image description here

Into a JSON format, like:

{
  "Inputs": {
    "input1": {
      "ColumnNames": [
        "age",
        "workclass",
        "fnlwgt",
        "education",
        "education-num",
        "marital-status",
        "occupation",
        "relationship",
        "race",
        "sex",
        "capital-gain",
        "capital-loss",
        "hours-per-week",
        "native-country"
      ],
      "Values": [
        [
          "0",
          "value",
          "0",
          "value",
          "0",
          "value",
          "value",
          "value",
          "value",
          "value",
          "0",
          "0",
          "0",
          "value"
        ],
        [
          "0",
          "value",
          "0",
          "value",
          "0",
          "value",
          "value",
          "value",
          "value",
          "value",
          "0",
          "0",
          "0",
          "value"
        ]
      ]
    }
  },
  "GlobalParameters": {}
}

This is supposed to be used as the body of a POST request to a web service.

So, I've tried applying the following function to the table above:

(InputData) =>
let
    JsonOutput = Json.FromValue(InputData),
    OutputText = Text.FromBinary(JsonOutput)
in
    OutputText

This is the full code:

let
    Origem = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HY2xDsIwDET/JTOWGtoSMcIOC0IMVQcrMdSS00huVIm/x2G4e3e64abJ9Wd3cI+KleBTdsshjP5kvGJcSIpuln1vdqedFDKqMiXrl5QhCilHlDaXCrzCGzPL1/pr4UrGG0rD0YfB0JmGZs/V5gT/583N8w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [age = _t, workclass = _t, fnlwgt = _t, education = _t, #"education-num" = _t, #"marital-status" = _t, occupation = _t, relationship = _t, race = _t, sex = _t, #"capital-gain" = _t, #"capital-loss" = _t, #"hours-per-week" = _t, #"native-country" = _t]),
    #"Tipo Alterado" = Table.TransformColumnTypes(Origem,{{"age", Int64.Type}, {"workclass", type text}, {"fnlwgt", Int64.Type}, {"education", type text}, {"education-num", Int64.Type}, {"marital-status", type text}, {"occupation", type text}, {"relationship", type text}, {"race", type text}, {"sex", type text}, {"capital-gain", Int64.Type}, {"capital-loss", Int64.Type}, {"hours-per-week", Int64.Type}, {"native-country", type text}}),
    Output = GetJson(#"Tipo Alterado")
in
    Output

But this is returning:

[{"age":39,"workclass":"State-gov","fnlwgt":77516,"education":"Bachelors","education-num":13,"marital-status":"Never-married","occupation":"Adm-clerical","relationship":"Not-in-family","race":"White","sex":"Male","capital-gain":2174,"capital-loss":0,"hours-per-week":40,"native-country":"United-States"}]
1
jsonpowerbipowerquery

1 Answers

1
votes

Based on the transformation you've described, might make sense to use the following 2 functions in particular:

  • Table.ColumnNames
  • Table.ToRows

Small example is below:

let
    source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "HY2xDsIwDET/JTOWGtoSMcIOC0IMVQcrMdSS00huVIm/x2G4e3e64abJ9Wd3cI+KleBTdsshjP5kvGJcSIpuln1vdqedFDKqMiXrl5QhCilHlDaXCrzCGzPL1/pr4UrGG0rD0YfB0JmGZs/V5gT/583N8w8=",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let 
            _t = ((type nullable text) meta [Serialized.Text = true])
        in 
            type table [age = _t, workclass = _t, fnlwgt = _t, education = _t, #"education-num" = _t, #"marital-status" = _t, occupation = _t, relationship = _t, race = _t, sex = _t, #"capital-gain" = _t, #"capital-loss" = _t, #"hours-per-week" = _t, #"native-country" = _t]
    ),

    CreateJsonPayload = (someTable as table) as binary => Json.FromValue([
        Inputs = [
            input1 = [
                ColumnNames = Table.ColumnNames(someTable),
                Values = Table.ToRows(someTable)
            ]
        ],
        GlobalParameters = []
    ]),
    // If you're doing the POST request via Web.Contents, think you can pass the return value of Json.FromValue
    // directly in as the Content field value.
    // This means you wouldn't need to do Text.FromBinary (at least not for the POST request).
    payload = CreateJsonPayload(source),
    // Below is just for debugging and sanity checking purposes.
    preview = Text.FromBinary(payload)
in
    preview

Some things to note:

  1. Your example shows numeric columns (like age) being encoded as strings (i.e. "0"). If you want the recipient to decode them as numbers, you can call Table.TransformColumns or Table.TransformColumnTypes to change the types (as appropriate) and then pass the transformed table to CreateJsonPayload.
  2. The CreateJsonPayload function accepts any table and returns a binary value (effectively just bytes) that represent the JSON. The function is just an example (based on the expected output you mentioned in your question). You can obviously refactor the function to make it a better, generalisable solution as appropriate for you.

Gives me the below, which I think matches your expected output:

Code output