Combine multiple excel files with different struct...

twister8889 · ‎09-15-2020

Hi guys,

I'm trying to combine some excel files with different struct columns, but I'm having a problem separating each column to a specific file, how can do I do this? I appreciate the help.

I'm using two functions:

1) by Chris Webb (https://blog.crossjoin.co.uk/2017/12/03/making-sure-all-columns-appear-when-you-combine-data-from-mu...) With this function I can update my table with all columns from my tables. Its works!

2) by @ImkeF to unpivot columns, Its works

I attached my pbix and excel file here: https://1drv.ms/u/s!AoDYwrtLrltJn1hpnlY_YJKbiVNC?e=hWy3gz

Basically my problem is, when I combine the excel files, I have the data from excel2 into excel1 line, in the example the CODE20 is from excel2 (Table2.xlsx) not excel1 (Table1.xlsx).

I would like to have one column with all CODES, one column with all Date1, and another column with all Date2 but each one to specific Source

v-xicai · ‎09-29-2020

Hi @twister8889 ,

Have the issue been resolved? If Yes, could you please show us the solution to help others having the similar issue and accept it as the solution ? We will appreciate you so much, thanks.

Best regards

Amy

v-xicai · ‎09-18-2020

Hi @twister8889 ,

You may need to make the original data table more effective and logical, for example you may select on the columns of table in excel, right click to choose Transpose to transpose the column to row , and do some other transform in excel to let the table display like the layout of picture 2, which makes the same property values be in one single column. After transforming both two excel tables to the layout of picture2, which will have similar table structure, you may later use combine the files in a folder into a combined table, see more steps:Combine files (binaries) in Power BI Desktop.

Picture1 :(Table1)

Picture2(with standard table layout):