Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Combining Files with Dynamic Date Columns in Query Editor

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: 

 

Item1/1/20201/2/20201/3/20201/4/2020
1500400300400
2600500400500

 

Table 2: 

 

Item1/2/20201/3/20201/4/20201/5/2020
1400300400300
2500400500350

 

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!!!

Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    Hi Anonymous,

     

    Import the folder and adapt the Transform File function accordingly.

     

     

3 Replies

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    Hi Anonymous,

     

    Import the folder and adapt the Transform File function accordingly.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        mwegener
        Most Valuable Professional
        6 years ago

        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.