Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vanessafvg
Super User
Super User

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




15 REPLIES 15
Vvelarde
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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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.

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.