Log In
Register
Home
October 27, 2020
Offline
I'm sure this is a simple query but I'm struggling.
I want to convert simple sales data that has 1 row for each Date but several products and Amounts in the adjacent cells.
Please find attached file if you want to have a crack.
[Image Can Not Be Found]
October 27, 2020
Offline
File
Moderators
January 31, 2022
Offline
The attached file contains a rather clunky (but working) solution I made up myself. It requires a certain amount of M-code manipulation but it's not too advanced.
Now you can do all this with more advanced techniques like custom functions and List.Generate. Search on-line for 'split multiple columns into rows power query'. You may find similar or even clunkier ones than mine.
Trusted Members
Moderators
November 1, 2018
Offline
Just as an alternative, you can split the two columns into lists and make a table out of them, then expand that table - see attached sample.
The following users say thank you to Velouria for this useful post:
Alan SidmanModerators
January 31, 2022
Offline
Nice! @Velouria
Trusted Members
Moderators
November 1, 2018
Offline
Thanks, though I can't claim credit. (unfortunately I've forgotten where I first came across that trick or I'd give proper attribution)
October 27, 2020
Offline
Thanks heaps for both responses.
Velouria's is nice. And simple. Just how I like it.
That's not to take away from Riny's Clunker. That was also helpful and introduced me to some concepts I hadn't considered before.
Thank you both.
1 Guest(s)
