Forum Discussion
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 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.
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!
3 Replies
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
DataStéphane
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!
Hi,
...Alcohol consumed_Percent = number ])
Stéphane
