Forum Discussion

sarthakgirdhar's avatar
sarthakgirdhar
Helper I
3 years ago
Solved

Need help converting multiple column groups to rows

Hi, I have a dataset/table that looks like this:-   * There is a Total_Pct column at the end which isn't shown in the picture above.   I wish to transform the table in Power Query such that...
Need Help
  • sarthakgirdhar's avatar
    sarthakgirdhar
    3 years ago

    Hi slorin / Stéphane,


    Thank you so much for the above code. I am still new to M, and I am unable to resolve this error message. Please take a look at the picture below.

     

    Thanks a lot in advance!

     

slorin's avatar
slorin
Super User
3 years ago

Hi,

 

let
Source = YourSource,
Sex = List.ReplaceValue(Table.ColumnNames(Source)," Freq.","",Replacer.ReplaceText),
Data = Table.FromRows(
   List.TransformMany(
      Table.ToRows(Source), 
      each {1,3,5,7},
     (x,y) => {x{0},Sex{y},x{y},x{y+1}}),
   type table [Frequency = text, Sex = text, Alcohol consumed_Number = Int64.Type, Alcohol consumed_Percent = type number ])
in
Data

 

Stéphane