EN VI

Powerbi - Stacking Multiple 2x2 Columns into Two Columns in Power Query?

How to Powerbi - Stacking Multiple 2x2 Columns into Two Columns in Power Query

_Hi, I have a table with multiple 2x2 columns (2&4, 6&8...), and I would like to stack those pairs into a two columns table while maintaining the correspondence. The output should be :

col2 | col4  
col6 | col8

Column2	Column4	Column6	Column8	Column10	Column12	Column14	Column16	Column18	Column20	Column22	Column24	Column26	Column28	Column30	Column32	Index
0.1APte	719141	2.19.1	13757	2.22.1	25169	2.29.1	59756	2.37.1	28892	2.45.1	27559	2.52.1	25981	2.58.1	13838	1
0.1AHPH	3972485	2.19.2	100596	2.22.2	206710	2.29.2	113129	2.37.2	29922	2.45.2	14862	2.52.2	117860	2.58.2	9099	2
0.1AHCH	2181012	2.19.3	50851	2.22.3	78663	2.29.3	25093,5	2.37.3	20291,4	2.45.3	24622	2.52.3	3969,35	2.58.3	3859,5	3
0.1AHPE	5850951	null	null	null	null	null	null	null	null	null	null	null	null	null	null	4
0.1AHCE	2623078	null	null	null	null	null	null	null	null	null	null	null	null	null	null	5
0.1RPte	102186	2.21.1	447467	2.27.1	34524	2.35.1	6872,6	2.43.1	7106,3	2.50.1	15506,9	2.56.1	1509,54	2.62.1	4620,6	6
0.1RHPH	504274	2.21.2	247933	2.27.2	13605	2.35.2	83707	2.43.2	9802	2.50.2	15855	2.56.2	8735	2.62.2	6686	7
0.1RHCH	245013	2.21.3	167623	2.27.3	7067	2.35.3	4936,4	2.43.3	2360,7	2.50.3	998,3	2.56.3	5254,1	2.62.3	1443,77	8
0.1RHPE	603041	2.21.4	9416	2.27.4	4539,09	null	null	null	null	null	null	null	null	null	null	9
0.1RHCE	176346	null	null	null	null	null	null	null	null	null	null	null	null	null	null	10
1A	15015887	2.20.1	7414	2.23.1	54168	2.30.1	91816	2.38.1	44462	2.46.1	64432	2.53.1	13721	2.59.1	12839	11
1R	2017513	2.20.2	15715	2.23.2	91088	2.30.2	48382	2.38.2	10127	2.46.2	54120	2.53.2	47040	2.59.2	64977	12
null	null	2.20.3	5451,2	2.23.3	34985,1	2.30.3	9680,66	2.38.3	16083,7	2.46.3	13806,6	2.53.3	830,21	2.59.3	5309,42	13
null	null	null	null	null	null	2.30.4		null	null	null	null	null	null	null	null	14
null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	15
2.1	4499234	null	null	2.24.1	13109	2.31.1	83690	2.39.1	78792	2.47.1	14548	2.54.1	9029	2.60.1	15176	16
2.1.1	470703	null	null	2.24.2	24882	2.31.2	99128	2.39.2	59697	2.47.2	30047	2.54.2	9221	2.60.2	13163	17
2.1.2	197983	null	null	2.24.3	11697,8	2.31.3	32974,5	2.39.3	25628,31	2.47.3	26269,1	2.54.3	510,5	2.60.3	0	18
2.1.3	540625	null	null	null	null	null	null	null	null	null	null	null	null	null	null	19
2.1.4	586121	null	null	2.28.1	43929	2.36.1	3578,9	2.44.1	7336,9	2.51.1	4538,46	2.57.1	4833	2.63.1	205	20
-	15417	null	null	2.28.2	6865	2.36.2	14440	2.44.2	8965	2.51.2	11613	2.57.2	5101	2.63.2	1770	21
-	0	null	null	2.28.3	2503,9	2.36.3	356,4	2.44.3	4302,9	2.51.3	1072,4	2.57.3	901	2.63.3	0	22
-	14807	null	null	null	null	null	null	null	null	null	null	null	null	null	null	23
-	0	null	null	2.25.1	106004	2.32.1	30008	2.40.1	27411	2.48.1	9420	2.55.1	15818	2.61.1	12877	24
2.2	75353	null	null	2.25.2	73211	2.32.2	13865	2.40.2	21162	2.48.2	10360	2.55.2	73194	2.61.2	16574	25
2.3	1047220	null	null	2.25.3	88667,8	2.32.3	27198,3	2.40.3	24432,8	2.48.3	1871,26	2.55.3	19610,1	2.61.3	6391,8	26
2.64	52959	null	null	null	null	null	null	null	null	null	null	null	null	null	null	27
2.65	49640	null	null	null	null	2.33.1	18455	2.41.1	12055	2.49.1	23720	null	null	null	null	28
2.66	7508,44	null	null	null	null	2.33.2	41280	2.41.2	4613	2.49.2	49549	null	null	null	null	29
2.67	14939	null	null	null	null	2.33.3	7027,8	2.41.3	8984,7	2.49.3	29086,6	null	null	null	null	30
2.68	7109	null	null	null	null	null	null	null	null	null	null	null	null	null	null	31
2.4	11776	null	null	null	null	2.34.1	22459	2.42.1	17352	null	null	null	null	null	null	32
2.5	25060	null	null	null	null	2.34.2	24207	2.42.2	14739	null	null	null	null	null	null	33
2.6	19052	null	null	null	null	2.34.3	17805,4	2.42.3	13523	null	null	null	null	null	null	34
2.7	17872	null	null	null	null	null	null	null	null	null	null	null	null	null	null	35
2.8	32964	null	null	2.26.1	293304	null	null	null	null	null	null	null	null	null	null	36
2.9	10844	null	null	2.26.2	478994	null	null	null	null	null	null	null	null	null	null	37
2.10	10657	null	null	2.26.3	227270	null	null	null	null	null	null	null	null	null	null	38
2.11	15348	null	null	2.26.4	445808	null	null	null	null	null	null	null	null	null	null	39
2.12	6773	null	null	null	null	null	null	null	null	null	null	null	null	null	null	40
2.13	1449,3	null	null	Phytoconrole	1365,09	null	null	null	null	null	null	null	null	null	null	41
2.14	55,1	null	null	null	null	null	null	null	null	null	null	null	null	null	null	42
2.15	4021,2	null	null	null	null	null	null	null	null	null	null	null	null	null	null	43
2.16	740027	null	null	null	null	null	null	null	null	null	null	null	null	null	null	44
2.17	434193	null	null	null	null	null	null	null	null	null	null	null	null	null	null	45
2.18	0	null	null	null	null	null	null	null	null	null	null	null	null	null	null	46
2.69	437517	null	null	null	null	null	null	null	null	null	null	null	null	null	null	47

Currently, my M code looks like this :

let
    Source = Excel.Workbook(Paramètre2, null, true),
    #"ELEC 10 (2)_Sheet" = Source{[Item="ELEC 10 (2)",Kind="Sheet"]}[Data],
    #"Colonnes supprimées" = Table.RemoveColumns(#"ELEC 10 (2)_Sheet",{"Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column5", "Column10", "Column15", "Column20", "Column25", "Column30", "Column35"}),
    #"Rempli vers le bas" = Table.FillDown(#"Colonnes supprimées",{"Column1", "Column6", "Column11", "Column16", "Column21", "Column26", "Column31", "Column36"}),
    #"Table transposée" = Table.Transpose(#"Rempli vers le bas"),
    #"Rempli vers le bas1" = Table.FillDown(#"Table transposée",{"Column1"}),
    #"Colonnes supprimées1" = Table.RemoveColumns(#"Rempli vers le bas1",{"Column2"}),
    #"Table transposée1" = Table.Transpose(#"Colonnes supprimées1"),
    #"Colonnes supprimées2" = Table.RemoveColumns(#"Table transposée1",{"Column1", "Column3", "Column5", "Column7", "Column9", "Column11", "Column13", "Column15", "Column17", "Column19", "Column21", "Column23", "Column25", "Column27", "Column29", "Column31"}),
    #"Premières lignes supprimées" = Table.Skip(#"Colonnes supprimées2",1),
    #"Index ajouté" = Table.AddIndexColumn(#"Premières lignes supprimées", "Index", 1, 1, Int64.Type)
in
    #"Index ajouté"

What additional code would be needed to accomplish this? Thanks in advance.

Solution:

You probably want to remove the index column first, but you can use this pasted into home ... advanced editor ...

change the groupsof number for number of columns you want to stack as a group, and change the base_columns number to be the number of leading columns you want to preserve before stacking the other columns

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
base_columns=0, groupsof=2, //change as needed
Combo = List.Transform(List.Split(List.Skip(Table.ColumnNames(Source),base_columns),groupsof), each List.FirstN(Table.ColumnNames(Source),base_columns) & _),
#"Added Custom" =List.Accumulate(Combo, #table({"Column1"}, {}), (state,current)=> state & Table.Skip(Table.DemoteHeaders(Table.SelectColumns(Source, current)),1))
in #"Added Custom"

in your particular case

    #"Premières lignes supprimées" = Table.Skip(#"Colonnes supprimées2",1),
    base_columns=0, groupsof=2, //change as needed
    Combo = List.Transform(List.Split(List.Skip(Table.ColumnNames(#"Premières lignes supprimées"),base_columns),groupsof), each List.FirstN(Table.ColumnNames(#"Premières lignes supprimées"),base_columns) & _),
    #"Added Custom" =List.Accumulate(Combo, #table({"Column1"}, {}), (state,current)=> state & Table.Skip(Table.DemoteHeaders(Table.SelectColumns(#"Premières lignes supprimées", current)),1))
    in #"Added Custom"