cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User I
Super User I

Data Shaping with M question

I have an excel file that is a template for inputting reports on fault equiment.

because its a form the data looks a lot like below, there is a category and then a value in the same role underneath, and in the same column are many categories with values.   How do you go about shaping this so that the category becomes the colum and the value underneath the value, it would be be easy if it was only one category per column but because there are multiple it is not so simple.  Also how can you grab the line after the category?

 

thanks!

 

 

I have Capture.PNG





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

Proud to be a Super User!




15 REPLIES 15
Community Champion
Community Champion

@vanessafvg

 

Hi, please try following the steps of the image:

 

PQ.gif

 

Regards

 

Victor

Lima - Peru




Lima - Peru

@Vvelarde  how can I slow down you demonstration video so I can follow along with the steps?  I am new to this and it goes too fast for me to absorb

@Vvelarde you rock will give that a go





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

Proud to be a Super User!




@Vvelarde ah sorry i actual over simlified the requirements, the issue is the issue of the column is spread across multiple columns that may or may not have data in them and may or may not have different fields

 

for example it looks a bit more like this

Capture.PNG

 

 





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

Proud to be a Super User!




@vanessafvg

 

Ohh, well I will try. in your scenario what is the expected result.

 

With 2 cases and 2 results will be very helpful to me and others.

 

Victor

 

 




Lima - Peru

Capture.PNG

 

expected result would be to have all fields assigned their own columns with their values if there is on and null if there isn't

 

as you could see with the work details, the value isn't always directly below the field name

 

does that make sense @Vvelarde

 

thank you again





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

Proud to be a Super User!




@vanessafvg

 

I almost close but now going to a meeting. 

 

But i attached my file. 




Lima - Peru

thank you so much, that was really useful i learned a lot, unfortunately once again trying ot protect the content of the data ive understated the requirements;

 

1) you can get more than one field on the same row so when you combine columns you have up to 4 columns on the same row with lots of gaps between

 

Capture.PNG

2) there are hundreds of these templates that need to  be combined, so doing a group by on the field name wont work because you wont know which form it came from

 

i learned a lot from what you posted  but struggling with the issues from above

this is an exapmle the actual data below , as i said here are many forms so thats just the first one sucked in by power query.

 

 

 

Capture.PNG





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

Proud to be a Super User!




@Vvelarde hi sorry i dont know if you saw the udpate to my post, sorry to be a pain in the butt, any idea you know how to handle the above scenario





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

Proud to be a Super User!




@vanessafvg

 

Hi, sorry for my delay.  Looking the complexity of your template / data structure maybe you'll need some Code directly in Advance Editor.   @

 




Lima - Peru

Hi @vanessafvg,

please post representative sample data of before and after.

Thanks and cheers, Imke

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF thanks will put something together tomorrow, not much to show for after to be honest because finding it hard to pull the data vertically and horizontally.

 

 





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

Proud to be a Super User!




After is important, because it is not clear to me from the screenshots you showed, which item belongs to wich header/category. 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF ah i see what you saying, the expected result,  yes makes absolute sense.





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

Proud to be a Super User!




@Vvelarde, nice one! I was having a go at this myself and was waiting for a solution.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors