☰

整合在一起

tag-icon • tag-icon microsoft-excel power-query microsoft-powerbi
整合在一起

我有如下格式的 JSON：

{
  "data": [
    {
      "date": "2021-01-01",
      "data": 10
    },
    {
      "date": "2021-01-02",
      "data": 20
    },
    {
      "date": "2021-01-03",
      "data": 30
    }
  ]
}

我可以编写这段代码，它完全满足我的要求：

let
    Source = Json.Document(...),
    data = Source[data],
    #"ToTable" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
// Block 1 Start
    Column1 = #"ToTable"{0}[Column1], //Gets Record from 1st "data" Array item
    #"ToTable1" = Record.ToTable(Column1), //Create table with two columns (key, value)
    #"Transpose1" = Table.Transpose(#"ToTable1"), //Transpose the table
    #"Headers1" = Table.PromoteHeaders(#"Transpose1", [PromoteAllScalars=true]), //Get headers from the 1st row (key names)
    #"ChangeType1" = Table.TransformColumnTypes(#"Headers1",{{"date", type date}, {"value", Int64.Type}, type any}}), //Change Type
// Block 1 End

// Block 2 Start
    Column2 = #"ToTable"{1}[Column1], //Gets Record from 2nd "data" Array item
    #"ToTable2" = Record.ToTable(Column2),
    #"Transpose2" = Table.Transpose(#"ToTable2"),
    #"Headers2" = Table.PromoteHeaders(#"Transpose2", [PromoteAllScalars=true]),
    #"ChangeType2" = Table.TransformColumnTypes(#"Headers2",{{"date", type date}, {"value", Int64.Type}, type any}}),
// Block 2 End

// Block 3 Start
    Column3 = #"ToTable"{2}[Column1], //Gets Record from 3rd "data" Array item
    #"ToTable3" = Record.ToTable(Column3),
    #"Transpose3" = Table.Transpose(#"ToTable3"),
    #"Headers3" = Table.PromoteHeaders(#"Transpose3", [PromoteAllScalars=true]),
    #"ChangeType3" = Table.TransformColumnTypes(#"Headers3",{{"date", type date}, {"value", Int64.Type}, type any}}),
// Block 3 End

    TheTable = Table.Combine({ChangeType1, ChangeType2, ChangeType3}) //Combines tables together
in
    TheTable

但

对于“数据”数组中的 3 个项目来说，这一切都没问题，但如果有数百个项目则不行。

是否有一些技巧可以Code block对中的每一行重复data[Column1]，并获取List要在中使用的表（ChangeTypeX）Table.Combine？

答案1

看起来您想要的结果包含两列date和data。如果是这样，您需要做的就是扩展步骤 3 中得到的表格。

只需单击列顶部的双箭头

enter image description here

M 代码

let
    Source = Json.Document(File.Contents("C:\Users\ron\Desktop\new 3.json")),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"date", "data"}, {"date", "data"})
in
    #"Expanded Column1"

结果
enter image description here

扩展列后，您还应该设置数据类型

答案2

让我们从头开始

let
    Source = Json.Document(...),
    data = Source[data],
    #"ToTable1" = Record.ToTable(Column1)

现在我们有ListsRecord存储在 中#"ToTable1"[Column1]。（因为表的列是Lists。）

所以我们可以使用List.Transform功能操纵每一个Item这个List。

语法是

List.Transform(list as list, transform as function) as list

因此我们需要一些转换函数，分别处理列表中的每个项目。让我们修改原始问题中的代码块。

MyFunc = (ColumnRow) =>
let
  #"ToTableX" = Record.ToTable(ColumnRow),
  #"TransposeX" = Table.Transpose(#"ToTableX"),
  #"HeadersX" = Table.PromoteHeaders(#"TransposeX", [PromoteAllScalars=true]),
  #"ChangeTypeX" = Table.TransformColumnTypes(#"HeadersX",{{"date", type date}, {"value", Int64.Type}, type any}})
in

#"ChangeTypeX"

所以整个List.Tranform函数代码如下所示

List.Transform(#"ToTable1"[Column1], MyFunc)

这将返回sList中的一个Table，可用于Table.Combine功能。

MyResultTable = Table.Combine(List.Transform(#"ToTable1"[Column1], MyFunc))

整合在一起

let
  Source = Json.Document(...),
  data = Source[data],
  #"ToTable1" = Record.ToTable(Column1)
// End of original code used here
  
  MyList = #"ToTable1"[Column1], //For better readability
  MyFunc = (ColumnRow) =>
  let
    #"ToTableX" = Record.ToTable(ColumnRow),
    #"TransposeX" = Table.Transpose(#"ToTableX"),
    #"HeadersX" = Table.PromoteHeaders(#"TransposeX", [PromoteAllScalars=true]),
    #"ChangeTypeX" = Table.TransformColumnTypes(#"HeadersX",{{"date", type date}, {"value", Int64.Type}, type any}})
  in
  
  #"ChangeTypeX",
  
  MyResultTable = Table.Combine(List.Transform(MyList, MyFunc))
   
  in
  
  MyResultTable

相关内容