json - M Power Query - 动态扩展列

Question

我正在尝试在转换为表格后扩展列，但是这些列需要动态扩展。

我在网上进行了研究，但我添加的代码并不能以一种或另一种方式工作。我一定错过了什么，但我不确定。

这是我的代码：

let
Date = DateTime.ToText((DateTime.LocalNow()), "yyyy-MM-dd"),
Date2 = Date.ToText((Date.AddDays(Date.From(DateTime.FixedLocalNow()),-90)), "yyyy-MM-dd"),
Source = Json.Document(Web.Contents("https://www.findmyshift.com/staff-view?r=2rr67u5y9i&apikey=***********&format=json&start=" & Date2 & "&finish=" & Date)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", List.Accumulate(#"Converted to Table"[Column1], {}, (state, current) => List.Union({state, Table.ColumnNames(current)}))),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Column1","'","'",Replacer.ReplaceText,{"Column1.Name"}),
#"Renamed Columns" = Table.TransformColumnNames(#"Replaced Value", (columnName as text) as text => Text.Replace(columnName, "Column1.", ""))

任何帮助将不胜感激。

标签： jsonpowerquery

Answer 1

尝试用以下代码替换步骤#“Expanded Column1” ：

= Table.ExpandRecordColumn(#"Converted to Table", "Column1",
Record.FieldNames(#"Converted to Table"{0}[Column1]))