Forum

Merge and Stack Mul...
 
Notifications
Clear all

Merge and Stack Multiple Sets of Columns dynamically

2 Posts
2 Users
0 Reactions
138 Views
(@sifar786)
Posts: 1
New Member
Topic starter
 

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 :

Input csv table

into this expected Output : Output table

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:

Percentage section   |     | Stats section             |     | Numbers section      |

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?

 
Posted : 07/11/2020 8:50 am
Philip Treacy
(@philipt)
Posts: 1632
Member Admin
 

Cross posted to here

https://community.powerbi.com/t5/Power-Query/Merge-Multiple-Sets-of-Columns-dynamically-to-Stack-them-one/m-p/1474909#M45939

 

Do not cross post without linking to the cross post to let us know

 
Posted : 07/11/2020 11:37 pm
Share: