CODEWITHSUNDEEP
powerbidaxpowerquery
n0e
n0e

Reputation: 309

Power BI: Split columns into rows

I have something similar to this:

enter image description here

what I want is to get a structure similar to this one:

Item   | Period | Qty
----------------------
Item A |   1    |  2
Item A |   2    |  2
Item B |   1    |  1
Item B |   2    |  1
Item C |   1    |  1
Item C |   2    |  1
Item D |   1    |  2
Item D |   2    |  2

So basically I want to split the values in columns into rows, but not merely transposing (at least in my understanding). How can I achieve that?

Upvotes: 1

Views: 345

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176104

Using UNPIVOT:

enter image description here

enter image description here

And extract LastCharacter:

enter image description here

Reordering and renaming:

enter image description here

Full M query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzXVU0lEyAuNYHYiIE5BnCMaxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, ABT_Period1 = _t, ABT_Period2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"ABT_Period1", Int64.Type}, {"ABT_Period2", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value"),
    #"Inserted Last Characters" = Table.AddColumn(#"Unpivoted Columns", "Last Characters", each Text.End([Attribute], 1), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Last Characters",{"Attribute"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Name", "Last Characters", "Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Last Characters", "Period"}, {"Value", "Qty"}})
in
    #"Renamed Columns"

EDIT:

As @Alexis Olson proposed in comment the easiest way to get Period number is usage of: Text.AfterDelimiter([Attribute],"Period")

Upvotes: 2

Related Questions