主页 / computer / 问题 / 1689105
Accepted
Lluser
Asked: 2021-11-24 06:30:04 +0800 CST

Excel：Power Query - 如何为表中的每一行重复代码块

  • 772

我的 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？

microsoft-excel power-query

2 个回答

  1. 让我们从头开始

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

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

    所以我们可以使用List.Transform函数来操作每个Itemthis 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)

    这将返回 a Listof Tables ，它可以在Table.Combinefunction中使用。

    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
    • 2
  2. Best Answer

    看来您想要一个包含两列date和的结果data。如果是这种情况，您需要做的就是展开第 3 步生成的表格。

    只需单击列顶部的双头箭头

    在此处输入图像描述

    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"

    结果
    在此处输入图像描述

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

    • 1

相关问题