Combining Files with Dynamic Date Columns in Query Editor

[Anonymous]

Hi!

I am looking to solve a problem I am having in query editor. I have multiple files which I am looking to combine that have the same structure but different column names. The column names are changed everyday when a new file is uploaded with the past day's information. I will try to summarize below.

Table 1: 

Item	1/1/2020	1/2/2020	1/3/2020	1/4/2020
1	500	400	300	400
2	600	500	400	500

Table 2: 

Item	1/2/2020	1/3/2020	1/4/2020	1/5/2020
1	400	300	400	300
2	500	400	500	350

As you can see in the example tables, each table reaches back a specific amount of days and displays total sold for each item. Each day a new file is added and the date range is pushed forward one day. Unfortunately, the data I am working with is confidential and that is the most amount of information I can provide.

I am looking for this combining to be fully automated. I was thinking "unpivot other columns" would be in the solution but I have not figured out the solution just yet. I would really appreciate any help I could get! Thank you in advance!!!

[mwegener · Accepted solution]

Hi @Anonymous,

Import the folder and adapt the Transform File function accordingly.

[Anonymous]

@mwegener Thank you very much for your help!

My takeaway from this is that you can essentially "unpivot other columns" on all of the files before the combining of them takes place.

Would you mind commenting more on this process other than the two screenshots or posting a link that can provide more guidance? The dataset I am working with is much more complicated than my example tables so I would just this solution to be scalable.

I really appreciate any help! Thank you very much!!

[mwegener]

Hi @Anonymous ,

what can I write about it?

1.  I imported the files from "Folder" using "Get Data".

2. I used the "Combine & Transform Data" option.

3. Then I modeled my transformation using the "Transform Sample File" query.

3. Then I copied the transformation into the "Tranform File" function using the "Advanced Editor".

Make sure that the transformation steps are valid for all files.

If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.