Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
  • shaowu459's avatar
    shaowu459
    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