Please need your help.
I have an excel file with this following structure, that one row is divided into the next two rows:
And need finally get this one. Every third row need to be combined into one long row formed from the next two rows:
thanks.
2 Answers
This formula uses TOROW and WRAPROWS
=LET(tab,G3:K11,
WRAPROWS(TOROW(tab),COLUMNS(tab)*3))
Set the tab variable to the source range.
The formula first create a single row of all the data, and then wrap it at the multiple of columns by three.
let
Source = Excel.CurrentWorkbook(){[Name="Tabelle2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Values", each if Value.Is([Column1],type number) then [Column1] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Values"}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Filled Down", {"Column11", "Column10", "Column9", "Column8", "Column7", "Column6", "Column5", "Column4", "Column3", "Column2", "Column1"}, "Attribut", "Wert"),
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Only Selected Columns", "Index", 1, 1),
#"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 33), type number),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Modulo",{"Index", "Attribut"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Cols Numbers", each if [Modulo] = 0 then 33 else [Modulo]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Modulo"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Cols Numbers", type text}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type1", "Cols", each "Column "&[Cols Numbers]),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Cols Numbers"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[Cols]), "Cols", "Wert"),
#"Removed Columns3" = Table.RemoveColumns(#"Pivoted Column",{"Values"})
in
#"Removed Columns3"
An alternative could be Power Query. This code turns the blue dynamic table into the green result table. You only have to enter data in the blue table and then refresh the green result table to return the updated result. The name of the table is "Tabelle2" in my example. Power Query works in legacy Excel versions such as Excel 2013 in which my sample was made. The green result table has 33 columns which aren't all shown in the screenshot.
