Need help with Nested Multiple Headers in PQ

[rakibahmed]

Hi experts
I am very much new to Power Query. I receive attached report every month in multiple workbook for multiple business. I want to use power query from folder. Tried for a week now watching videos and blogs but could not figure out yet. Need your help please.

 

[UK_GER]

Hi,

what do you want to figure out?

How to pull data from different files out of a folder?

and 2nd question:


How should any result out of your action look like. Looking at the file, I'm not able to guess what this could look like.


Some more specific information are required here.


UWE

 

[rakibahmed]

Hi UWE
Thanks for giving attention to the post.
There will be similar file every month for 1 property (only month name will change). There are multiple properties. So we are talking about 12 files for each property in a year multiplied by number of properties. I need to consolidate and analyse. Hence, I want to use power query to unpivot the data and save as query and excel simple table so that I can use the data to create various reports using pivot table.

 

[UK_GER]

Hi,

even I'm still not 100% sure what the result should look like, my first step would be to learn how to pick up files from folder.
So you can have 1 folder for each property and copy the monthly new files into that folder.

As I'm sitting in front of BI and not Query I can only guide you rough.

You have to find the button for "New Source" or "Data from Source" or something like this.
(Might be that it is "hidden" somewhere under "more...".)

You need to find the menu point "from folder".


There you have to type in(or search for) the path, where your data is stored.
After "OK" a dialog will show up, where you can see the files in the folder.

Hit the button "Combine and edit"

Mark Sheet1 and press ok. (Sheet1 is the name of the sheet in your file)

PQ will do the rest for you. You can see the steps in the query settings.

----
So copy two files (with the same structure, but different name) into one folder and try out.


Good luck

Uwe

 

[rakibahmed]

Hi UWE
Perhaps I was not detailed enough for you. But if i am not mistaken I have posted my request in correct section. Actually I have done those but stuck with so many headers.

Allow me explain further my challenges:
-As you can see in the file there are 3 sections (Occupancy%, ADR and RevPAR) with subsection % Changes for each of these headers.
-Above sections data are across month by month including last year for 12 months. Then for Year-to-date. Table further goes for running 3 months, then again for running 12 months.
-I also tried to set it with index column and modulo but could not make it work as the data distance (vertically is not same)

I can also use power BI desktop if you wish to use that to help me out of it. I am not familiar with M code except promoting headers or converting binary to table simplest alike.

Attached file is only one sheet of the file. There are more sheets in there. I was wondering if I could solve and learn this problem, I can solve other sheets myself. I want attached file in a table and future proof query so that I can add more files to folders and use those data for analysis, dashboard, slices, graphs etc. for reporting purposes.

Hope this time it is clear to you!

• Forum
• "Power" Technology Help
• Power Query (Get & Transform) & M code

 

[UK_GER]

Hi,

now i understand more, but the easiest way would be if you could send one file that shows how the result should/could look like.
That would be great.

Uwe

 

[rakibahmed]

Hi Uwe
Thanks for making me optimistic.
Here attached the would/could be sample output. By the way, while preparing the table I felt there will be complications on Year to Date, Running 3 Months and running 12 months. Nevertheless, lets give a try!

 

[rakibahmed]

I should have mentioned that most complicated (at least for me) thing in this report is the way the data being received. Let me explain; Attached is January 2018 report (reporting month is very important) you will know soon-why.

1. 2016 data starts from August and will be starting from September in February 2018 report and will start from October in March 2018 report and will gradually disappear with increment of year 2018 months.
2. 2017 data (Jan to Dec) will be repeated every month until year 2018 ends.

 

[UK_GER]

Hi,

i've no access to PQ or BI for the rest of the day. Will have a closer look tomorrow.

Uwe

 

[UK_GER]

Hi,

had a closer 2h look and tried to find the first steps on the way.

But to be honest - I'm not able to open the nut.

Sorry for this.

Maybe someone else with more experience in PQ and FI/CO knowledge can help you find a way to build a solution.


So who can, pls. step in.

Uwe

 

[rakibahmed]

Hi,

had a closer 2h look and tried to find the first steps on the way.

But to be honest - I'm not able to open the nut.

Sorry for this.

Maybe someone else with more experience in PQ and FI/CO knowledge can help you find a way to build a solution.


So who can, pls. step in.

Uwe

So, no one else?