Connection from Folder, but want to Unpivot before Combine with different time period Header

[smurakam0201]

I have multiple files in one folder.

Same sturucture but different time period as header.

What I want to do is Unpivot each file and flatten, then combine. Image is like below.

(Extremely simlifided example)

Goal

Because of other restrictions, I want to keep original individual excel data as non-table.

[Greg_Deckler]

Right, so use a Folder query and in that folder query you can do your unpivot operations on an example/sample file and it will then perform those operations on all files and then append everything together.

[PaulAllen]

Greg, thank you for you comments about un-pivot and combine when using get data with a folder.  I am not able to make this work in the situation described in this thread by smurakam0201.  If all three files had identical column headers this would be a piece of cake.  However, because the headers in columns B, C, and D are different in each file,  if I perform the un-pivot with the data as it is first imported, the headers are Column1, Column2, Column3... and the un-pivot yeilds meaningless data.  If I promote the first row to the header, then it works perfectly on the sample file but fails on the other two files as it is looking for a column header that doesn't exist in the other two files.  This is a very common need for anyone doing forecasting in which each month a new file is produced with the same number of columns but with the YrMonth column headers shifting by one month in each file (dropping the oldest month and adding the newest).  Any help you can provide to solve this would be greatly apreciated!

[PaulAllen]

I found a very simple solution that works perfectly.  Thought I'd share it with others who are trying to resolve this delima... https://youtu.be/tpK_xklbDf0 

[User5231]

This is a solution to the proposed problem. I also used this method to unpivot tables before expanding the combined files. Very elegant.

[smurakam0201]

Hi Greg,

Thank you for your answer.

But what is 'Folder query'?

The original file is Excle 'Sheet' (Not force to convcert this to 'table' at this point).

And I need to add columns with below procedure.

Go to the Add Column tab → Add Custom Column Enter the following formula: =Excel.Workbook([Content]) Click OK

to expand the contents, then I am seeing missalighend headers.

If you have time, could you guide bit more in detail?

Thnaks,

Shin

[dax]

Hi @smurakam0201 , 

If your each table is same structure like below

 You could try to follow below steps. You could connect to folder , choose corresponding folder and click "combine and transform"

 Then change something in function like below(prompt header and delete change type, choose the name column and click unpivot columns), then change this in invoked table to delete change type

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smurakam0201]

Thank you for the suggesiton.

I tried that at first. But It returens the error as below.

As I mentioned initially,I don't want to convert 'Range' to 'Table' as starting point, because users want to keep filters and formats as curernt excel functions.

Keeping Excel Sheet and Combine, I followed to add custome column to avoid thi serror, then Transform before Combine is gone.

That is the conflict and my strugle.

FYI)

My versoin's screen.

I want to confirm one more thing with your sugestion (Probably converting range to table works??).

I need to prepare 'sample' excel in same folder with 'Full Range of time period' on header.  Corect??

Thanks,

Shin

[dax]

Hi @smurakam0201 , 

Where did this error occur in steps of first image? Did this cuase by other file (like .txt or other file which not excel)? If so, you could filter it in invoked table like below to see whether it work or not

If this doesn't work, you could upload your excel files(you could use virtual data  instead of real data). Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smurakam0201]

HI Zoe,

Thank you for the reply.

Unfortunately, the company policy does not allow to use dropbox or google drive and I cannot attach any file from current environment.

My private PC does not have MS applications, but I will think some workaround.

Takes some time though. 

Regards,

Shin

[smurakam0201]

All,

I tried couple of things and here is a summary of my findings.

1.  Change each file's range to table.

2.  Create sample file with FULL lit of other file's header (My case this is time period header)

Then Combine and transform worked.

Trials failed were

1. Using normal sheet without converting range to Table

2. Using other file as sample which does not have FULL list of time horizon in header.

I will consider these preparation is reasonable for existing users or not.

Still waiting some better idea, without changing Excel file/sheet itself.

Thank you,

Shin