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 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
Solved! Go to 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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
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.
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
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
So simple and clever! I learned something new and useful today, thank you!
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.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |