合并/合并两个标题行

Question

对于1000多个商店的列表，我有不断变化的标题行(列顺序正在变化(，我需要将前两行合并为一个标题。

这是一个简化的示例表，包含3个不同的商店和4周的数据(实际数据>40000行，每个行88列(

第2列>第3列2135商店品牌00/00182产品B00/00182商店品牌000/0018200/0018211,9600/0018200/00182<1>>00/00205>产品B产品B00/0020500/0020500/0020500/002055，09<1>>>商店品牌01>-10店铺品牌0周>产品A00/09002<1d>Sc价值<11>423，36>店铺品牌125，453，9542560商店品牌114,9728,98>店铺品牌18,98>>店铺品牌1

第0列	第1列		第4列		第5列	>第6列	>	11列	索引
产品		周	产品A	产品B
数据类型		Sc金额	Sc价值	Sc利润	Sc价值	Sc利润		360
周	202201						361	商店品牌0
周	20222	4	4
周	202203	5	14,95	8
周	202204	6,49	1,5
产品		周	产品A		400	商店品牌0
数据类型		Sc金额	Sc价值	Sc利润
周	202201						402	商店品牌0
周	20222						403	商店品牌0
周	202203	1	0，79	6，49	1，5	404
周	00/00205	202204	0	0	-19，19	6,49	405
产品	00/09002	产品A
数据类型		Sc金额	Sc价值	Sc利润	Sc利润	42558
周	00/09002	202201	2	3,22	5,88	42559
周	00/09002	20222		5
周	00/09002	202203	3	2.09	0,48	42561
周	00/09002	202204	2	0,48	7	33,83	42562

组合row1/row2并使所有列的列名：

#"NewNames" = Table.AddColumn(Table.Transpose(Table.FirstN(Source,2)), "Custom", each Text.Trim([Column1]&":"&[Column2]))[Custom],
#"Rename"=Table.RenameColumns( Table.Skip(Source,2), List.Zip( { Table.ColumnNames( Source ), #"NewNames" } ) )

或与上述相同，但随后将前3个列名单独指定为特殊情况：

FirstFew={"A","B","C"},
#"NewNames1" = Table.AddColumn(Table.Transpose(Table.FirstN(Source,2)), "Custom", each Text.Trim([Column1]&":"&[Column2]))[Custom],
#"NewNames" = FirstFew & List.Skip(#"NewNames1",List.Count(FirstFew)),
#"Rename"=Table.RenameColumns( Table.Skip(Source,2), List.Zip( { Table.ColumnNames( Source ), #"NewNames" } ) )

更新答案

我不明白你为什么要为分组而烦恼。看起来你可以一次完成，然后应用过滤器来拉出坏行

<snip>
#"Reordered Columns" = Table.ReorderColumns(Source, {"Index","Shopbrand","Column0","Column1","Column2","Column3","Column4","Column5","Column6","Column8","Column11" }),
NewNames = Table.AddColumn(Table.Transpose(Table.FirstN(  #"Reordered Columns", 2)),"Custom",each Text.Trim([Column1]& " : "& [Column2]))[Custom],
rename_headers = Table.RenameColumns(Table.Skip(  #"Reordered Columns", 2),List.Zip({Table.ColumnNames(  #"Reordered Columns"),{"Index","Shopbrand","delete-able","shop-nr","Week"}& List.Skip(NewNames, 5)})),
#"Duplicated Column" = Table.DuplicateColumn(rename_headers, "shop-nr", "shop-nr - Copy"),
#"Reordered Columns2" = Table.ReorderColumns(#"Duplicated Column",{"shop-nr", "Index", "Shopbrand", "delete-able", "shop-nr - Copy", "Week"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns2", each ([#"delete-able"] = "Week"))
in  #"Filtered Rows"

尝试

1. 转座表
2. 连接前2列
3. Transpose back

这是我的最终解决方案，包含了上面的@horseyrdes答案。

let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"rZnNbtw2FIVfJZg1B+X9kUQuE/QBCgRoF0YWRuKiRdo4COxF3r7iJeURZ3itU8CAxxZk+ZAiv3N5SN/dnX778fjl+fPTKZxi/CVGSrxe/vHw8HX90X73/ujyg3spU16/f/zr8fuHH/ffvsR3p0/h7vTr/dP908/vD32j5cHP797/+/j87ale/37/z/NDvVw1//z7CXtE5jhotL3TrkGOzJFqy1dfMhMuUS50/RCFPLfrnRLjSrJeTEVJQy4/U68kuJL1Z/3MQcsMUJh6KcWlppHUzQ2ZJ1xy3nem3E0hl3eNQZNpzbjW4kzggkskRyLhEtmRGOE/lqDojbPWGzFwmTVZcLrJoXvB6SbGpn/BMSdxeoXzTepI4FzT5EjgHNPsSOD4koPvguNLDr4Lji8ZX+1zlpCuygfXKcZh5iOYz+VJSTjK7KCccJSZ939ZO2OFMAaxspNwhtlhOOEM82WQqY7RknstHGZ2YE44zOzAnHCY2YE54TCzA3PCYWanFiccX4ljiYzzKg6vGedV2JHAMRUH04xjKk6pzTid4tCZcTrFoTOP6LzKtDXCvFmm1Tii4DrT1kZPb5VpNR5xUxt8JdNqPOJmJzFGT+MRNzuJ8mjpyRRijS9W3m4WBY1HIO00rVq3z5lyK5wXybOlKI1HYO0kx3GmXC0hl8KlwYq6xqNCuBMtj1pwaomWglViu0WhNCfBVjSNR7Vxp9qV18FAHtXInVQ6kDqqlTup3F4sB1uF1nneZr4PrkpH1fMiSuMCrIS7oM++t69IuBvsUTaUbZtyZgq6vSSHbORp2/wRbhEnCCvhjnCCsBLuACcIK+G8O0FYCYfbCcJKONROEFbCYaZxdlDG0WUHXcbRtUdvDbTTwtm9Db0tbp55LczJ1HBindirjBPLXQ3nOVg6vM3ByjjCTvxVxhF24q8yjrATf5VxhJ34q4wj3MffSpHFvVg3Oyo4y04OVsFZdnKwCo6wHOzbVHCAnTSrgsPmpFkVHDYnzaqMYOvTbI7xTU9oV5tNXas0zLOt2dOb5dm12TRodjdercmXRNuHp+3QlSXYaE4hpaabcd2OLJudKagFwHoAu8r1h8mvyxUOLRdojY4cXtJRf8JZdAnX1aFIaUGkplN5ef3+vPl13fKKZfCEgp1UTsHOnexgYql5JrZYswoLLjyfhhl6MIWrruK6S+swp1CZqDnMOpxDHfJpm7gJ101b52Kw3LhmRPIHYsaF84bp2tOKqb2Djc2aR+26EleER0YcC9d9TuXfpnEK82aS3JbUwG0dnXGnEe1GYq4j4WxmijBuNf8c+WqXwNOCO65P0GM13Gd9mB7CuuDuovGOz+4uldKpnrQWXdxctJsPqvNhpfQK1lYOFtxdFsGbsJ2Cc+gW76KGe6p27XrCOzHcR24unxbcM24ynxbcIM45dBHBzcDbhpK4HT1fpnDdW1qB0SCNjYT7geWiYolW2kJ0sv15XTyWVscS7oyXs+qr5FXuploh1vltHkm4R/rgPmhgVcOdYW98u5MYy+K+4GUoUsd0qndyK7UJNwinYW+bAet6GcyTRRf3Cuf/MQq4eyT2Iuct5IyFcUfJPtzZzbqo2xo5VVo5GKxFGHeZbC7ryR/2N+Me68/UW8qx38pLRMi4tWQLd1eTPioPqzDuLbmcbA7/v1TUcG/JZehSXWAtb7S026KCtFyQb8z16T8=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true]            )
in
type table [Column0 = _t,Column1 = _t,Column2 = _t,Column3 = _t,Column4 = _t,Column5 = _t,Column6 = _t,Column8 = _t,Column11 = _t,Index = _t,
Shopbrand = _t
]
),
#"Reordered Columns" = Table.ReorderColumns(
Source, {"Index","Shopbrand","Column0","Column1","Column2","Column3","Column4","Column5","Column6","Column8","Column11" }
),
// Grouping and expanding based on this solution here https://stackoverflow.com/a/73800993/1440255
#"Grouped Rows" = Table.Group(
#"Reordered Columns",
{
"Column1"
},
{{
"storetables",
each
_,
type table [Column0 = nullable text,Column1 = nullable text,Column2 = nullable text,Column3 = nullable text,Column4 = nullable text,Column5 = nullable text,Column6 = nullable text,Column8 = nullable text,Column11 = nullable text,Index = nullable text,Shopbrand = nullable text]
}}
),
// helper only for debuging to see one of the stores tables
#"00/00182" = #"Grouped Rows"{[Column1 = "00/00182"]}[storetables],
// go through all tables in storetables and join the first two rows and use them as new headers
#"newshop" = Table.TransformColumns(
#"Grouped Rows",
{
{
"storetables",
each
let
// join the first two rows with " : "
NewNames = Table.AddColumn(
Table.Transpose(Table.FirstN(_, 2)),
"Custom",
each Text.Trim([Column1]& " : "& [Column2])
)[Custom],
// rename the headers, but keep the first 5 from the fixed list below
rename_headers = Table.RenameColumns(
Table.Skip(_, 2),
List.Zip(
{Table.ColumnNames(_),
{
"Index","Shopbrand","delete-able","shop-nr","Week"
}
& List.Skip(NewNames, 5)
}
)
)
in
rename_headers
}
}
),
// Epand all storetables back into one beautiful table
#"expand storetables" =
let
ColumnNames = Table.ColumnNames(
Table.Combine(#"newshop"[storetables])
),
ExpandColumns = Table.ExpandTableColumn(
#"newshop",
"storetables",
ColumnNames
)
in
ExpandColumns
in
#"expand storetables"