Need help converting multiple column groups to rows

[sarthakgirdhar]

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 it looks like this:-

Frequency	Sex	Alcohol consumed_Number	Alcohol consumed_Percent
Never used	Male	3732	23
Never used	Female	6941	19
Never used	Unknown	142	33
Never used	Total	11113	20.2
Have used, but not in last 30	Male	2608	16
Have used, but not in last 30	Female	6403	17
. . .

How can I do that? Any help would be greatly appreciated.

[slorin]

Hi,

...Alcohol consumed_Percent = number ])

Stéphane 

[sarthakgirdhar · Accepted solution]

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]

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