Hello All!
Firstly let me tell you I have tried for hours to work out how to do this and am very hopeful to lend on the brainstrust here for a solution.
Attached is the current and desired outlay of data I am currently working with. I can get rid of unnecessary columns etc. but struggling to get the repeating "Job, Customer, 1000X, Total for Job into columns. Tried pivot, tranform but could not get it to work.
Please help 
Thanks,
Troy
Job billing data.xlsx (11.2 KB)
Hi @Troy,
Couldn’t quite match the columns with the desired lay out but this is what I got for you now.
It took me a few rounds to get it right. I ended up splitting the Value section from the other Data and bringing them back together again in the end.
The tricky part was the Data labels because they didn’t all line up and were split into multiple rows.
Well here’s my solution file, if you update the FileLocation parameter all queries will be restored.
I hope this is helpful.
eDNA - Navision repeating row data.pbix (44.2 KB)
Wow! Thank you so much! Would never have got there that way. Just wondering and I should have put this into the example, but how would you treat the information if there were more than one task description i.e. the line below customer could be more than one. Same type of index method?
No, you’ll be in trouble then…
I used the Number.Mod expression because your data was evenly structured think of it as a fixed offset in between items. So if the number of lines aren’t fixed and could also vary, a new key is required to bring the data back together again. That will probably have to be a combination of Task, Job and an Index so we can make sure repeated tasks are handled properly.
But this will not only impact the Values but also de Data labels, since that will now longer be a single row…
Can you post a sample? Then I will try to look at it again tonight.
Thanks - I am trying to use an index and merge query method similar to what you had in your solution.
Example attached with more information and the non-regular structure. Ideally would need all lines, however have the total, job number and customer in one line is also ok
Many thanks,
Troy
Job Billing Data 2.xlsx (13.9 KB)
Hi Troy,
Okay so with the experience from yesterday fresh in mind, this was resolved pretty quickly…
I didn’t expand the key but split the data in a slightly different way - so I didn’t have to ‘create’ multiple rows - cause that would be sorted by the Merge operation later on.
So here’s my solution file. if you update the FileLocation2 parameter the queries will be restored.
I hope this was helpful.
eDNA - Navision repeating row data.pbix (62.2 KB)
Wow Melissa - I cannot thank you enough! This is perfect and I have learnt some very useful techniques from you
Thanks,
Troy
