使用Power / Power查询从表到JSON

Question

我需要转换下表：

​

​

转换为JSON格式，例如：

{
  "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": {}
}

这应该用作对web服务的POST请求的主体。

因此，我尝试将以下函数应用于上表：

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

这是完整的代码：

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

但这件事正在回归：

[{"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"}]

Answer 1

根据您所描述的转换，特别是使用以下两个函数可能是有意义的：

• Table.ColumnNames
• Table.ToRows

下面是一个小例子：

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

有些事情要注意：

1. 您的示例显示数字列(如age)被编码为字符串(即"0")。如果希望接收方将它们解码为数字，可以调用Table.TransformColumns或Table.TransformColumnTypes (酌情)更改类型，然后将转换后的表传递给接受任何表的CreateJsonPayload函数，并返回表示JSON的二进制值(实际上只是字节)。这个函数只是一个例子(基于您在问题中提到的预期输出)。显然，可以重构该函数，使其成为适合您的更好、更通用的解决方案。

给出以下内容，我认为这与您的预期输出相匹配：

​

​