Best Practice For Data Structure (Power BI & Power Query)

%3CLINGO-SUB%20id%3D%22lingo-sub-1857508%22%20slang%3D%22en-US%22%3EBest%20Practice%20For%20Data%20Structure%20(Power%20BI%20%26amp%3B%20Power%20Query)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1857508%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20been%20provided%20with%20the%20attached%20dataset%20and%20I'm%20wondering%20what%20the%20best%20way%20is%20to%20structure%20it.%26nbsp%3B%20I'm%20planning%20to%20pull%20this%20into%20Power%20BI%26nbsp%3Band%20want%20to%20do%20a%20bar%20graph%20that%20has%20Actual%20and%20Budget%20data%20bars%20with%20%24's%20as%20the%20bar%20value%20and%20months%20on%20the%20X-axis.%26nbsp%3BI%20tried%202%20approaches.%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20The%20first%20approach%20was%20to%20unpivot%20all%20of%20the%20columns%20at%20once.%20%26nbsp%3BI%20created%20a%20new%20column%20to%20distinguish%20between%20actual%2C%20budget%2C%20and%20forecast%20and%20had%20all%20of%20the%20numbers%20in%20the%20same%20column.%26nbsp%3B%20This%20is%20a%20clean%20way%20to%20structure%20the%20data%20but%20the%20problem%20I%20have%20is%20that%20because%20all%20of%20the%20numbers%20are%20in%20the%20same%20column%20so%20I%26nbsp%3Bcouldn't%20graph%20Actual%20%26amp%3B%20Budget%20%24's%20side%20by%20side%20because%20they%20are%20part%20of%20the%20same%20field.%3C%2FP%3E%3CP%3E2)%20The%20second%20approach%20was%20to%20unpivot%20the%20actuals%2C%20forecast%20%26amp%3B%20budget%20columns%20separately%20(so%20there%20would%20be%20separate%20queries%20for%20each%20of%20these).%26nbsp%3B%20This%20gives%20me%20dates%20and%20%24's%20for%20each%20category%20(ie.%20Actuals%2C%20Forecast%20%26amp%3B%20Budget)%26nbsp%3B%20This%20allows%20me%20to%20place%20each%20category%20in%20a%20separate%20set%20of%20bars%20and%20I%20can%20place%20a%20date%20at%20the%20bottom%2C%20which%20is%20almost%20what%20I%26nbsp%3Bwant.%26nbsp%3B%20The%20problem%20is%20that%20I%20now%20have%203%20different%20dates%20columns%20and%20I%20require%20one%20common%20date%20column%20instead%20of%203%20different%20ones.%26nbsp%3B%20I%20thought%20about%20using%20a%20calendar%20table%20and%20connecting%20the%20data%20tables%20by%20date%2C%20but%20I'm%20wondering%20if%20there's%20a%20simpler%20way%20to%20approach%20this.%26nbsp%3B%20I%20feel%20like%20there%20is%20a%20simple%20solution%20to%20this%2C%20but%20I'm%20just%20not%20sure%20how%20to%20tackle%20it%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ELindsay%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1857508%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20Apps%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

I've been provided with the attached dataset and I'm wondering what the best way is to structure it.  I'm planning to pull this into Power BI and want to do a bar graph that has Actual and Budget data bars with $'s as the bar value and months on the X-axis. I tried 2 approaches. 

1) The first approach was to unpivot all of the columns at once.  I created a new column to distinguish between actual, budget, and forecast and had all of the numbers in the same column.  This is a clean way to structure the data but the problem I have is that because all of the numbers are in the same column so I couldn't graph Actual & Budget $'s side by side because they are part of the same field.

2) The second approach was to unpivot the actuals, forecast & budget columns separately (so there would be separate queries for each of these).  This gives me dates and $'s for each category (ie. Actuals, Forecast & Budget)  This allows me to place each category in a separate set of bars and I can place a date at the bottom, which is almost what I want.  The problem is that I now have 3 different dates columns and I require one common date column instead of 3 different ones.  I thought about using a calendar table and connecting the data tables by date, but I'm wondering if there's a simpler way to approach this.  I feel like there is a simple solution to this, but I'm just not sure how to tackle it

Thanks

Lindsay

0 Replies