cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
electrobrit
Helper V
Helper V

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

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)

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

 

This is the goal:

Screen Change
AD
BC
BM
BR
PJ
PS
  

 

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

1 ACCEPTED SOLUTION
parry2k
Super User III
Super User III

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

5 REPLIES 5
parry2k
Super User III
Super User III

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Ashish_Mathur
Super User III
Super User III

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
AlB
Super User III
Super User III

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 

 

SU18_powerbi_badge

parry2k
Super User III
Super User III

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.