Often when working in Excel you get all kinds of flattened files that come from all kinds of different source and you want to use as them as a source for your analysis. Today we are looking at how DataExplorer can help you with this. Click here to download DataExplorer, for more details on Data Explorer check out my previous blogs.
Image we have the following Products with revenue for the following fiscal year and month. This data comes from your nicely structured datawarehouse:
Your managers, who asked for this report, wants to see these values combined with the target values. You get these values delivered in the following pivoted text file:
The problem with a format like this is that it is not easy to pivot on. In order for us to to do analytics on top of this we need to reshape the data into a shape where the months and years become a value, not a header. This is called Unpivotting.
So how do I do this ? And how do you get these numbers into one combined Pivottable ? The answer here is Data Explorer. I select import from CSV in DataExplorer and point to the CSV file.
First I double click on Query and rename the query to revenueTarget to give the query a proper name, next I select that I want the First row as headers:
select all the columns that contain values and right mouse click, select Unpivot columns:
This will give us the result we want where year and month have now become values instead of column headers:
Now I rename columns to something sensible. In my PowerPivot model I need to combine this data with a date column so I would like to add that as well. I right mouse click on the date column, press Insert Column and select Custom
Here I can enter a value that will create a new date field:
=Text.End([YearMonth],2) & “/1/” &Text.Start([YearMonth],4)
And I Change the type to Date:
Ok the transformations are completed. Press Done and Add the results to the data model:
Now to add this to the data model I need d to create relationships, I do this by creating relationships to the DateTable and Product. I am essentially treating this new table a secondary fact table:
Now to combine values from the Invoice table and RevenueTarget into one KPI I go back to the datagrid view, select the RevenueTarget column and click autosum on the ribbon to create a measure for Sum of RevenueTarget. I also create a measure for that will serve as the base measure for our KPI. I add the following measure:
KPI:=[Sum of RevenueAmount]
Now I right mouse click and select KPI, I select the Sum of RevenueTarget as target value:
Now adding this KPI to the Pivottable we had in the beginning and voila:
