Power Query M语言高级编辑器操作后6-9组订单字段未显示问题求助

问题根因 #

你当前的代码存在步骤依赖错误：

• 处理第7、8、9、10组的拆分步骤，全部是基于#"Changed Type15"这个上游步骤执行，而非承接上一个分组处理完的结果
• 第6组处理完成后的结果（#"Changed Type17"）没有被后续步骤引用，每一组的修改互相独立、后续修改会覆盖前面的操作，因此最终仅保留了最后执行的第10组处理后的字段

修正方案 #

把每一步的上游依赖替换为前一个分组处理完成的输出即可，修正后的M代码如下：

#"Changed Type16" = Table.TransformColumnTypes(#"Split Column by Delimiter8",{{"Order Items.6.1", type text}, {"Order Items.6.2", Int64.Type}, {"Order Items.6.3", Int64.Type}}),
#"Renamed Columns10" = Table.RenameColumns(#"Changed Type16",{{"Order Items.6.2", "Order Quantity.6.2"}, {"Order Items.6.3", "Order Price.6.3"}}),
#"Changed Type17" = Table.TransformColumnTypes(#"Renamed Columns10",{{"Order Price.6.3", Currency.Type}}),
// 第7组处理承接第6组的结果
#"Split Column by Delimiter9" = Table.SplitColumn(#"Changed Type17", "Order Items.7", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Order Items.7.1", "Order Items.7.2", "Order Items.7.3"}),
#"Renamed Columns11" = Table.RenameColumns(#"Split Column by Delimiter9",{{"Order Items.7.2", "Order Quantity.7.2"}, {"Order Items.7.3", "Order Price.7.3"}}),
#"Changed Type18" = Table.TransformColumnTypes(#"Renamed Columns11",{{"Order Quantity.7.2", Int64.Type}, {"Order Price.7.3", Currency.Type}}),
// 第8组处理承接第7组的结果
#"Split Column by Delimiter10" = Table.SplitColumn(#"Changed Type18", "Order Items.8", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Order Items.8.1", "Order Items.8.2", "Order Items.8.3"}),
#"Renamed Columns12" = Table.RenameColumns(#"Split Column by Delimiter10",{{"Order Items.8.2", "Order Quantity.8.2"}, {"Order Items.8.3", "Order Price.8.3"}}),
#"Changed Type19" = Table.TransformColumnTypes(#"Renamed Columns12",{{"Order Quantity.8.2", Int64.Type}, {"Order Price.8.3", Currency.Type}}),
// 第9组处理承接第8组的结果
#"Split Column by Delimiter11" = Table.SplitColumn(#"Changed Type19", "Order Items.9", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Order Items.9.1", "Order Items.9.2", "Order Items.9.3"}),
#"Renamed Columns13" = Table.RenameColumns(#"Split Column by Delimiter11",{{"Order Items.9.2", "Order Quantity.9.2"}, {"Order Items.9.3", "Order Price.9.3"}}),
#"Changed Type20" = Table.TransformColumnTypes(#"Renamed Columns13",{{"Order Quantity.9.2", Int64.Type}, {"Order Price.9.3", Currency.Type}}),
// 第10组处理承接第9组的结果
#"Split Column by Delimiter12" = Table.SplitColumn(#"Changed Type20", "Order Items.10", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Order Items.10.1", "Order Items.10.2", "Order Items.10.3"}),
#"Renamed Columns14" = Table.RenameColumns(#"Split Column by Delimiter12",{{"Order Items.10.2", "Order Quantity.10.2"}, {"Order Items.10.3", "Order Price.10.3"}}),
#"Changed Type21" = Table.TransformColumnTypes(#"Renamed Columns14",{{"Order Quantity.10.2", Int64.Type}, {"Order Price.10.3", Currency.Type}})

如果后续需要处理更多分组，还可以用List.Generate批量生成处理逻辑，避免重复编写相似代码。

内容的提问来源于stack exchange，提问作者LordMagnarBucketHead