Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have some data in this format:
Id | Question1 | Value1 | Question2 | Value2 | Question3 | Value3 |
aaaa | Pears | 4 | Apples | 2 | Oranges | 5 |
aaab | Oranges | 2 | Pears | 5 | Apples | 7 |
aaac | Kiwi | 1 | Oranges | 8 | Apples | 3 |
The options for each of the questions are the same. The values are all numerical.
What I would like to be able to do is to transform it to be in this format with multiple rows where the question options are all in one column and the values are all in one column.
Id | Question | Option | Value |
aaaa | Question1 | Pears | 4 |
aaaa | Question2 | Apples | 2 |
aaaa | Question3 | Oranges | 5 |
aaab | Question1 | Oranges | 2 |
aaab | Question2 | Pears | 5 |
aaab | Question3 | Apples | 7 |
aaac | Question1 | Kiwi | 1 |
aaac | Question2 | Oranges | 8 |
aaac | Question3 | Apples | 3 |
I cannot seem to find a way to achieve this. Does anyone have any suggestions please? Thanks!
Solved! Go to Solution.
Unpivot all columns except ID.
Select the Attribute column. Split it by delimiter (from Non-digit to digit).
Then Pivot the column Attribute.1 with Value in Values and choose Don't Aggregate in the advanced section.
@asm495 Here is another way to solve this, a bit complex, but good to know how to do purely using M language.
let
Source = Table.FromRows (
Json.Document (
Binary.Decompress (
Binary.FromText (
"i45WSgQCJR2lgNTEomIgbQLEjgUFOakgjhEQ+xcl5qWDeaZKsTpg9UkowkZIuk2RdZvD1CcDOd6Z5ZlAyhBFqwWycmOl2FgA",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
in
type table [
Id = _t,
Question1 = _t,
Value1 = _t,
Question2 = _t,
Value2 = _t,
Question3 = _t,
Value3 = _t
]
),
ChangedType = Table.TransformColumnTypes (
Source,
{
{ "Id", type text },
{ "Question1", type text },
{ "Value1", Int64.Type },
{ "Question2", type text },
{ "Value2", Int64.Type },
{ "Question3", type text },
{ "Value3", Int64.Type }
}
),
AddedCustom = Table.AddColumn (
ChangedType,
"Custom",
each
let
TargetCols = Record.RemoveFields ( _, "Id" ),
RecToList = Record.ToList ( TargetCols ),
Questions =
List.Select (
Record.FieldNames ( TargetCols ),
each Text.StartsWith ( _, "Question" )
),
SplitIntoGroups = List.Split ( RecToList, 2 ),
ToRecords =
List.Transform (
SplitIntoGroups,
each Record.FromList ( _, { "Option", "Value" } )
),
Result =
Table.FromColumns (
{ Questions } & Table.ToColumns ( Table.FromRecords ( ToRecords ) ),
type table [ Question = text, Option = text, Value = Int64.Type ]
)
in
Result,
type table [ Question = text, Option = text, Value = Int64.Type ]
),
RemovedOtherColumns = Table.SelectColumns ( AddedCustom, { "Id", "Custom" } ),
ExpandedCustom =
Table.ExpandTableColumn (
RemovedOtherColumns,
"Custom",
{ "Question", "Option", "Value" },
{ "Question", "Option", "Value" }
)
in
ExpandedCustom
@asm495 Here is another way to solve this, a bit complex, but good to know how to do purely using M language.
let
Source = Table.FromRows (
Json.Document (
Binary.Decompress (
Binary.FromText (
"i45WSgQCJR2lgNTEomIgbQLEjgUFOakgjhEQ+xcl5qWDeaZKsTpg9UkowkZIuk2RdZvD1CcDOd6Z5ZlAyhBFqwWycmOl2FgA",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
in
type table [
Id = _t,
Question1 = _t,
Value1 = _t,
Question2 = _t,
Value2 = _t,
Question3 = _t,
Value3 = _t
]
),
ChangedType = Table.TransformColumnTypes (
Source,
{
{ "Id", type text },
{ "Question1", type text },
{ "Value1", Int64.Type },
{ "Question2", type text },
{ "Value2", Int64.Type },
{ "Question3", type text },
{ "Value3", Int64.Type }
}
),
AddedCustom = Table.AddColumn (
ChangedType,
"Custom",
each
let
TargetCols = Record.RemoveFields ( _, "Id" ),
RecToList = Record.ToList ( TargetCols ),
Questions =
List.Select (
Record.FieldNames ( TargetCols ),
each Text.StartsWith ( _, "Question" )
),
SplitIntoGroups = List.Split ( RecToList, 2 ),
ToRecords =
List.Transform (
SplitIntoGroups,
each Record.FromList ( _, { "Option", "Value" } )
),
Result =
Table.FromColumns (
{ Questions } & Table.ToColumns ( Table.FromRecords ( ToRecords ) ),
type table [ Question = text, Option = text, Value = Int64.Type ]
)
in
Result,
type table [ Question = text, Option = text, Value = Int64.Type ]
),
RemovedOtherColumns = Table.SelectColumns ( AddedCustom, { "Id", "Custom" } ),
ExpandedCustom =
Table.ExpandTableColumn (
RemovedOtherColumns,
"Custom",
{ "Question", "Option", "Value" },
{ "Question", "Option", "Value" }
)
in
ExpandedCustom
I have tested this and this also works well. One thing that anyone else who uses this solution should be aware of is that it is required for the sequence of the original columns to be: Question,Value,Question,Value,...
My data wasn't in this sequence and I had to correct this before it would work.
Thank you for the reply, it is appreciated. I have not had the chance to try this as I was trying the simpler solution offered first. I will try and test this later also to see if it works.
Unpivot all columns except ID.
Select the Attribute column. Split it by delimiter (from Non-digit to digit).
Then Pivot the column Attribute.1 with Value in Values and choose Don't Aggregate in the advanced section.
This is brilliant and seems to do exactly what I need. In my real life scenario I did not have the columns so neatly named with numbers but I adapted the solution by splitting on a different delimiter.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.