let
// Reports
tab_report = Table.FromRecords({
[Reports = "1 Report", Index = 0],
[Reports = "2 Report", Index = 1],
[Reports = "3 Report", Index = 2]
}),
reports = Table.TransformColumnTypes(tab_report,{{"Reports", type text}, {"Index", Int64.Type}}),
// Artikelnummern
Quelle = Excel.CurrentWorkbook(){[Name="tbl_artikelnr"]}[Content],
transf_columns = Table.TransformColumnTypes(Quelle,{{"Artikelnummer", type text}}),
added_Column1 = Table.AddColumn(transf_columns, "tmp1", each 0),
added_Column2 = Table.AddColumn(added_Column1, "tmp2", each 1),
added_Column3 = Table.AddColumn(added_Column2, "tmp3", each 2),
added_Index = Table.AddIndexColumn(added_Column3, "Spalte A", 0, 1),
transf_columns1 = Table.TransformColumnTypes(added_Index,{{"Spalte A", Int64.Type}}),
unpivot = Table.UnpivotOtherColumns(transf_columns1, {"Artikelnummer", "Spalte A"}, "tmp", "Index"),
removed_column = Table.RemoveColumns(unpivot,{"tmp"}),
// Zusammenführen
joined_tables = Table.NestedJoin(removed_column, {"Index"}, reports, {"Index"}, "die_reports", JoinKind.LeftOuter),
expanded_rows = Table.ExpandTableColumn(joined_tables, "die_reports", {"Reports"}, {"Reports"}),
removed_column2 = Table.RemoveColumns(expanded_rows,{"Index"}),
sorted = Table.Sort(removed_column2,{{"Spalte A", Order.Ascending}, {"Reports", Order.Ascending}}),
reordered = Table.ReorderColumns(sorted,{"Spalte A", "Artikelnummer", "Reports"})
in
reordered