Creating a report starting from two tables with different layouts

[mollino]

Hello, I'm new to Power BI, I'll try to be as clear as I can. 

Let's say I have a certain amount of WBSs with a cost associated to them. I want to create a report which compares the planned cost and the actual cost, these costs will be grouped by WBS, month and so on. I plan on producing a certain amount of graphs; I gather these informations from two different tables: both have WBS L1, L2, L3 as columns, but while the planned one has one additional column for each month and the costs in the corresponding column, the actual one has a "date" column and a "cost" column.

The first one is structured much like a pivot table whilst not being one. This means that the wbs listed in this table are univocal, the other one may have the same wbs listed in multiple rows, since there is a cell with the date of reference in the corresponding column. 

Is it possible to give these two tables to Power BI as input, and automatically pair the corresponding WBS with both costs, planned and actual, divided by month and year?

I made a simple excel to better give the idea of the tables' structure. I hope this is clear for everyone who reads.

Actual: 

Planned: 

Thanks in advance

[NaveenGandhi · Accepted solution]

To address the 1st part, Unless the first 3 columns[WBS L1, WBS L2, WBS L3] stays constant, you can use the "unpivot other columns" options in PowerQuery to do the job for you.

As far as the dates, Whatever the date range is from, You can use Power Query to make it start of everymonth or StartofMonth Dax function to create a column.

If you still have doubts or questions, Can you provide a Sample file or image on how exactly current structure is?

Thanks,
NG
 If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

[NaveenGandhi]

Hi 

You can unpivot the planned table into similar structure as actual. The table structure will be something like below[you can make the month column as start date using dax or power query]. Make sure to keep the date in actauls to start date of the month as well. 




You can create a model using the new planned, actual and a date table using start of the month field in these two tables and perform the calculation you are looking for.

Does this satisfy your requirement? If you have any questions in implementation, Let me know.
 If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

[mollino]

Hello, thank you for your response.

Regarding the planned table, I thought about something similar too, the only problem being the fact that months go from 23/03 to 31/12. Every cost paired with a WBS would sum up to roughly 9000 rows. Did you figure out a way to unpivot the planned table automatically? Furthermore, the table is subject to periodical change, so it is a conversion that needs to be done more than once. 

The date in the planned table are at the 1st of each month, while on the actual it tends to be from 20th to 28th. It shouldn't be an issue as long as the level of detail I need for the reports is month by month, is that correct?