Hi,
I have quite complex task for quite big table. I am trying to transform my table from :
to
My data is plain text and there is no fixed number of rows every paragraph.
RUI- sample for key .txt (7.9 KB)
RUI - sample for key .xlsx (31.5 KB)
Would be grateful for help and guidance.
Iwona
You can try unpivot only selected columns (you can find it at Transform → Unpivot Columns → Unpivot only selected columns) .If you need more dynamic solution you can see excellent Melissa’s video at: https://www.youtube.com/watch?v=aEezNicgHkE
Hope it helps.
Hi @mspanic
Maja,
Many thanks for taking time and offering your help.
I should show how original data look like:
Could I please to perform these steps in this sample in attached PBI. I am afraid , I don’t understand
RUI - sample.pbix (16.0 KB)
Would be grateful for your input.
Iwona
Hi Iwona,
I insert unpivot transform step in your PBI ( as prerequisits I split columns to transform txt comma delimiter format into columns that we can transform and use later in PowerQuery / PowerBI + I rename columns to Duty1 - Duty7 so it can be more understandable what happened with Unpivot transform step)
RUI - sample_Unpivoted_Example.pbix (43.5 KB)
Hope this helps.
Maja
Hi Maja,
Would it be possible to add as a first step something what gives this result below :
Thank you for your time and engagement.
Iwona
Hi Iwona,
Of course it’s possible -
I add Filtered rows step to filter what you need.
RUI - sample_Unpivoted_Example.pbix (42.1 KB)
I will be absence for a while.
If you need more filters just right click on the data
and choose what you need.
There are some blank rows if you need to remove them - right click on column distribution part → Remove Empty ( but check before other columns in that row) :
if you need more calculations (like Replace Values) you can find it at right click on the column name or you can find it at Transform ribbon at:
I could not put everything in the post. But Enteprise DNA has excellent Data transformation & Modeling course with lots of practical examples and detailed guidelines :
For the first time transformations in PowerQuery can seem hard to understand but step by step you can do it. They are like puzzles - build one on the top of another.
Hope it helps.
Good luck.
Maja
Thanks for the detailed response @mspanic!
Hi @Iwona! Did the response provided solve your query? If so, kindly tag the post by @mspanic as “solution”. Thanks!
Hi @Iwona, we’ve noticed that no response has been received from you since April 27th. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @Iwona, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
