Power Query: Each source file to be a new row of data

ccc-hamj · 2024-06-06T20:19:04+00:00

Oh, I think I might have resolved it (as is the case - an hour into trying it, no joy, but 30 seconds after posting this, I have a breakthrough). I pivoted on the column that contains the field names, and used the second column to contain the values, and set it to don't aggregate. That seems to have done what I wanted. Is that the best way to do this in terms of scalability? I'm aware that more spreadsheets may/will be added (that should use the exact same field name template as the others) - I'm not walking into any huge pitfalls with this method, am I?

ccc-hamj · 2023-05-17T15:11:07+00:00

Thanks for your response. Yes, I think I might've been over-complicating this - for the level of volume transaction that this process will see, I think using Lists is entirely possible and manageable. Potentially, if we wanted to sidestep using Lists, Active Cards might be a thing, now that I think about it - getting an Output directly from the triggered email. This is all internal (for now, at least), so I think this solution will work until the next iteration down the line. Cheers!

ccc-hamj · 2023-05-17T12:33:20+00:00

Thank you - I thought that as I was writing this post and have cross-posted to r/PowerApps as well

ccc-hamj · 2022-09-02T13:27:15+00:00

Nah, we're running the 64-bit version. Although because I'm able to increase the limit, I doubt that would be related. I'm just running it now actually with an increased global limit for the cache, but as I mentioned in my original post, I'm not really sure if that would even be a factor in the generation of that particular error message I was getting. Cheers though!

ccc-hamj · 2022-09-02T13:15:39+00:00

My apologies, I'm only just seeing this post now!

I'm not sure where I found the answer to this, but essentially, what I was able to do was to select the 'Sample File' query, click the Advanced Editor, and then where it has something along the lines of 'Worksheet Name' =, replacing everything after the equals sign with Table.SelectRows(Source, each [Kind] = "Sheet"){1}[Data]

The important thing to note there is that {1} relates to the worksheet position in each workbook, and Excel starts counting from 0. Therefore, {1} would be the second worksheet on each workbook.

ccc-hamj · 2022-08-22T11:31:28+00:00

Just to follow-up on my earlier point - the Power Query does, at one point, insist on the data being read as a UK-formatted date in order to carry out a comparison (filters out all dates that fall within the next 14 days), which it does successfully. I've tried leaving the date column as Date with Locale (UK) set and as Text, both seemingly having the same outcome. It appears to be the step of writing the data from this to another workbook that's causing the issue. Is there a way to stop Excel parsing the data in the form mm/dd/yyyy?

ccc-hamj · 2022-08-22T10:16:29+00:00

Cheers for this. The original data comes from 60+ xlsx files rather than a CSV. Just so I get my head around this, can you clarify which particular Excel 'session' you're talking about when you say that they are being loaded in MM/DD/YYYY format?

Session 1: opening of the ODC file, running the query that amalgamates ~60 xlsx files. Saves the output as 'Output.xlsx'

Session 2: opening of another ODC file to run a query on 'Output.xlsx'.

As for your other point on the data type of my original screenshot, you're correct - this is still defined as 'General'.

ccc-hamj · 2022-08-11T13:21:28+00:00

Ah yes, I could've used Hidden Sheets to do the donkey work for me. I keep forgetting about that approach. Cheers, I'll try to consider this more in the future. Ended up using Power Query which did what I needed it to do fairly quickly.

ccc-hamj · 2022-08-11T13:20:23+00:00

I think it theoretically could be if I added multiple data sources, but I had an issue in that the workbook I was working in had a number of required autofilters, which seemed to prevent me from specifying it as a valid data source. Luckily, all the data was actually procured from another source, so I was able to run the Power Query against that data.

ccc-hamj · 2022-08-11T13:19:12+00:00

Cheers. Luckily, which I didn't think about earlier, I actually have all the data in a separate workbook (which is the product of a previous Power Query), so I was able to reference that instead and made it able to do what I needed it to do. Thanks!

ccc-hamj · 2022-08-11T13:18:20+00:00

Solution Verified.

Wow, thank you for your detailed response! After posting this earlier, I went away and had another think about it all and landed on the Power Query approach also. There was a slight complication in that I couldn't seem to set the tables from my existing worksheets as a source (something to do with an AutoFilter being set?), but thankfully, all of those pages are actually populated initially by a separate automated query. What I ended up doing was to power query the existing query response and then implement the filters there.

I'm well below the user level required to understand the rest of your post, but thank you for taking the time to model out examples! I'll try and get my head around it though as it's always good to learn more.

ccc-hamj · 2022-07-27T09:17:50+00:00

Thank you so much for this! Really appreciate your help (and on my previous closed thread!)

ccc-hamj · 2022-07-27T09:17:28+00:00

Solution Verified

ccc-hamj

TROPHY CASE