Power BI: Split columns into rows

Question

I have something similar to this:

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?

Lukasz Szozda · Accepted Answer · 2020-12-15T21:40:00.893

2

Using UNPIVOT:

And extract LastCharacter:

Reordering and renaming:

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")

edited Dec 15 '20 at 21:40

answered Dec 15 '20 at 21:10

Lukasz Szozda

162,964
23
234
275

wow, this is great, just a question, I have periods up to 13 (2 digits), how do I overcome this in extracting last character(s) step? – n0e Dec 15 '20 at 21:22
or nevermind, I could rename those with single digit, and extract 2 digits – n0e Dec 15 '20 at 21:23
@n0e Another option:If column has the same prefix you could use remove/replace it `each Text.Remove([Attribute], {"A", "B", "P", "T", "_", "d", "e", "i", "o", "r"}` – Lukasz Szozda Dec 15 '20 at 21:24
2

You could also use `Text.AfterDelimiter([Attribute],"Period")`. – Alexis Olson Dec 15 '20 at 21:37
@AlexisOlson Yes, that is the most clean solution, Fun fact I used "Column From Examples" and the proposed one was replacing :) – Lukasz Szozda Dec 15 '20 at 21:38
@LukaszSzozda maybe you have some idea concerning my other SO question? https://stackoverflow.com/questions/65314541/power-bi-how-to-merge-specific-tables – n0e Dec 16 '20 at 06:33

Power BI: Split columns into rows

1 Answers1