Forum Discussion
Anonymous
5 years agoNot applicable
Merge Multiple Sets of Columns dynamically to Stack them one below the other - Power Query
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...
- 5 years ago
according to discussion, final code is:
let Source = Table.PromoteHeaders(Csv.Document(File.Contents("C:\Users\Clark\Desktop\SampleCSV.csv"),[Delimiter=",", Columns=36, Encoding=1252, QuoteStyle=QuoteStyle.None])), title = Table.ColumnNames(Source), count = List.Count(List.Select(title,each Text.StartsWith(_,title{5}))), num = (List.Count(title)-3)/count, Group = Table.SelectRows( Table.Combine( Table.Group( Source, "Que", {"n",each let header={"Que","Question","Answer","Label","Agg"}&List.Range(title,4,num-1), a=Table.ToRows(_), b=List.FirstN(a{0},2)&{a{1}{1}}, c=List.TransformMany( List.Skip(a,2), each List.Split(List.Skip(_,3),num), (m,n)=>b&List.Range(m,1,2)&List.Skip(n)) in #table(header,c) }, 0, (x,y)=>Byte.From(Text.Length(y)>0) )[n]), each Text.Length([Label])>0 ) in Group
Anonymous
5 years agoNot applicable
This is how i get it (same as before) after making your suggested change :
shaowu459
5 years agoResolver II
according to discussion, final code is:
let
Source = Table.PromoteHeaders(Csv.Document(File.Contents("C:\Users\Clark\Desktop\SampleCSV.csv"),[Delimiter=",", Columns=36, Encoding=1252, QuoteStyle=QuoteStyle.None])),
title = Table.ColumnNames(Source),
count = List.Count(List.Select(title,each Text.StartsWith(_,title{5}))),
num = (List.Count(title)-3)/count,
Group = Table.SelectRows(
Table.Combine(
Table.Group(
Source,
"Que",
{"n",each
let
header={"Que","Question","Answer","Label","Agg"}&List.Range(title,4,num-1),
a=Table.ToRows(_),
b=List.FirstN(a{0},2)&{a{1}{1}},
c=List.TransformMany(
List.Skip(a,2),
each List.Split(List.Skip(_,3),num),
(m,n)=>b&List.Range(m,1,2)&List.Skip(n)) in #table(header,c)
},
0,
(x,y)=>Byte.From(Text.Length(y)>0)
)[n]),
each Text.Length([Label])>0
)
in
Group- Anonymous5 years agoNot applicable
Genius! 🙂
Very clean and elegant code. Thank you very much.