Excel Forum

Merge and Stack Multiple Sets of Columns dynamically

sifar forever

New Member

Members

Forum Posts: 1

Member Since:
November 6, 2020

Offline

November 6, 2020 - 10:50 pm

I have a single csv dataset that looks like below. I have multiple csv files like this which i want to combine once i figure out the correct transformation for a single file. Here is the Sample Excel File containing this data :

into this expected Output :

The Input data has broadly 4 sections :

1] The 1st 3 columns are used to create Questions, Answers & Label columns.

2] a section of Percentage columns.

3] a section of Stat-testing columns.

4] a section of 1 columns.

I want to Merge each set of columns R.1....R10 and then Unpivot (Stack) them one above the other as shown in the output. The R1...R10 column counts may vary each time, but they will be the same for each section i.e. for Percentage columns, Stat testing columns & 1 columns.

While googling, i am came across an Imke Feldmann post on Merging and unpivoting columns dynamically using a custom function. However, i am unable to select and merge the different sections of columns dynamically. Also, i found Bill Szysz's post which is somewhat similar and trying to figure out a way to modify his 2nd solution i.e. Combine Method on a similar post. Here is Bill Szysz's Combine Method solution.

let Source = Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name="UglyData"]}[Content], [PromoteAllScalars=true]), Lists = Table.FromColumns({List.Transform({0..((List.Count(Table.ColumnNames(Source))-6)/5)-1}, each List.Range(Table.ColumnNames(Source), _*5+6, 5)) }), AlmostReady = Table.Combine(Table.AddColumn(Lists, "Columns", each Table.FromColumns({Table.Column(Source, Table.ColumnNames(Source){0})} & {List.Repeat({[Column1]{0}}, Table.RowCount(Source))} & List.Transform(List.Skip([Column1], 1), each Table.Column(Source, _)), {Table.ColumnNames(Source){0}, "Name"} & List.Transform(List.Skip([Column1], 1), each Text.BeforeDelimiter(_, "_")) ) )[Columns]), Ready = Table.AddColumn(AlmostReady, "Total", each List.Sum(List.Skip(Record.ToList(_), 2))) in Ready

The problem is, Bill uses fixed column and row counts ( 5 & 6) whereas my columns and rows count could change. for e.g. today there are 10 sets of columns per section, tomorrow there may be 5 or 20. But the columns count will always be the same across each section i.e. for Percentage section, Stats section and Number section. Also, the sequence of the Columns may change but maintain the same sequence across the sections:
e.g:

R1 | R3 | R2 | R5 | R4 | | R1 | R3 | R2 | R5 | R4 | | R1 | R3 | R2 | R5 | R4 |

This data is tricky and beyond my amateur skills. Is this possible to do?