How to import 2 blocks of data from a single Excel File?

[Bishak]

Hi All,

I was wondering if anyone had a challenge like mine and managed to success. We receive customer forecast in a weird format.

It is an Excel spreadheet, data is pivoted and in two block of 3 weeks, one under the other. The biggest issue is that row count between the blocks and files may change.

The below is a snip of I get the data. Target is to combine both blocks and unpivot them.

Any suggestions would be much appreciated
thanks

[Bishak]

Many thanks for your suggestion, however this does not resolve my issue, I should have been more precise.
I need to pick these files up from  a folder, therefore need a solution that will work without manual intervention.

I want use the created date as a forecast date and then compare.

The ony thing I fail on is how to 'split' these two blocks of data where amount of line items within are variable.

thanks

Bish

[v-yalanwu-msft]

Hi., @Bishak ;

1.In Excel, set the two modules to only from table.

2.In PowerBI, connect this excel.

3.Transform it and In power query,use first row as Headers.

Then Select all columns except code and name then unpivot.

4.Perform the same operation in Table2.

5.append two tables.

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.