rows to columns and keep the order of the rest of the data

[electrobrit]

I have data I need to transform to be able to use it for reporting. I have played with pivoting and unpivoting. I may not be doing it correctly if that is the solution.
This is what the data looks like. It's in order this way so screen A changed to D, screen B, changed to C, M, R. It's not the most efficient, if one column gets out of order the entire thing is out of order. 
(This is 2 columns)

Orig	Description
Screen:	A
Change:	D
Screen:	B
Change:	C
Change:	M
Change:	R
Screen:	P
Change:	J
Change:	S

This is the goal:

Screen	Change
A	D
B	C
B	M
B	R
P	J
P	S

How do I do this? any ideas?
thank you in advance

[parry2k]

@electrobrit glad you find it useful and helped you learned new things. Thanks!

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Orig", type text}, {"Description", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Screen", each if Text.Start([Orig],6)="Screen" then [Description] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Change", each if Text.Start([Orig],6)="Change" then [Description] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Description"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns",{"Screen"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Orig] = "Change:")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Orig"})
in
    #"Removed Columns1"

Hope this helps.

[AlB]

Hi @electrobrit 

Place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4uSk3Ns1LSUVIAAUelWJ1oJeeMxLz0VLigC1gQTaUTNpXO2AR9sQkGYTMzAJtKL2yCwUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Orig = _t, Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Orig", type text}, {"Description", type text}}),

    colNames_ = List.Transform(List.Distinct(#"Changed Type"[Orig]), each Text.BeforeDelimiter(_,":")),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Orig"}, {{"Res", each [Description] }}, GroupKind.Local), 
    finalT_ = Table.Combine(List.Transform(List.Split(#"Grouped Rows"[Res],2), each Table.FillDown(Table.FromColumns(_, colNames_), {colNames_{0}})))
in
    finalT_

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[parry2k · Accepted solution]

@electrobrit in attached solution, see screen table and the page 5. Screen table has some transformation which you can tweak as you see fit.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[electrobrit]

So simple and clever! I learned something new and useful today, thank you!