Forum Discussion
Merge multiple excel files into different tabs of one workbook using Power Query on Mac
Here is the variant, but made on Windows
Hi,
Below is a link for sample files.
Those 3files are the raw data.
Those two are the formats that I need to transform my separate 3 files into.
I need the data to be arranged into "expected_results1.xlsx" or "expected_results1.xlsx" because we have a tool (written in csharp) that accepts those two formats and uploads the data into our internal database.
I ended up solving this problem by updating the csharp code in our tool to accommodate multiple files in a one folder.
However, I am still posting the question here because I am sure this sort of issue will come up again in the near future.
Those files are sent by our clients, and there is no way to ensure that they will send us data in a consistent format.
Instead of updating csharp script every time they send us files in a different format, I am trying to find out an easier way to transform excel files into a certain format that our csharp tool can process.
The reason I ask about Power Query is that I came across below youtube video showing that I can use "Get Data"->"From Folder" in power query for this sort of situation:
https://www.youtube.com/watch?v=EpVQyXD5bV4
However, in my Office365 on mac, I see no such thing as "Get Data from Folder":
Re:there is no way to ensure that they will send us data in a consistent format.
As to result2.xlsx.
If I were you,I would like regular expression and sql to do this kind of ETL(extract transform and load).
select regexp2('^.*(?=\.)',f01) f01,f03,f04 from consolidateSheet where regexp('^\d+$',f04)
- Hi,
Can you let me know the app you are using in your video?