Molding a SQL Source in Power Query

[jaydend]

Hi,

For context, I am trying to create a simple dashboard that will compare the actual availability % of an asset fleet for each day of the week against a target.  This would be used to report whether we are meeting targets each day of the week.

For this argument's sake, say I have the below actuals supplied from an SQL analysis service;

Asset Fleet	Monday	Tuesday	Wednesday	Thursday
Haul Units	90	89	86	92
Dig Units	85	91	87	68

But our target was supplied as the below from an excel source;

Asset Fleet	Monday	Tuesday	Wednesday	Thursday
Haul Units	90	90	85	90
Dig Units	90	95	90	75

My desire is to have a 'difference' table on the dashboard that will simple show the actuals and colour it green if above target or red if below target.

My first issue is that the SQL query has a variation in 'asset fleet', in my case it has separated Dig units into Front End Loader AND Shovel Excavator.  Do I combine this in the query somehow or do I perform that action in the visual?

My next issue is that the SQL query lists the days of the week as another column, how can I set these to column headers in the visual?  I have read another forum that mentioned a solution is to create a query for each day that references the original and then somehow use these queries for the visual.

I may be missing information so please let me know if I can clarify further.

Thanks

Additional Info:

SQL Query Columns - 'Calendar Year', 'Day of Week', 'Production Week of Year', 'Asset Type', 'Site - Location', '% Avail.'

Excel Query Columns - 'Asset Type' , each day of the week

[Icey]

Hi @jaydend ,

Please share us some sample data with the same structure of your real data.

Best regards

Icey

[jaydend]

Hi @Icey 

I have filtered the query to only 2020, the first 2 weeks and 1 asset type otherwise it would be huge.  The first row is actually headers as it pulls from the SQL source.  The real data has all years, weeks, assets etc as I want the end user to page level filter for the week they want.

Date.Calendar Year	Date.Day Of Week	Week - Day	Date.Production Week Of Year	Asset Type	Location.Site	% HME Availability
2020	Monday	1	Wk 02 (Prod)	Haul Trucks	Site	77.01%
2020	Tuesday	2	Wk 02 (Prod)	Haul Trucks	Site	80.73%
2020	Wednesday	3	Wk 01 (Prod)	Haul Trucks	Site	76.34%
2020	Wednesday	3	Wk 02 (Prod)	Haul Trucks	Site	75.28%
2020	Thursday	4	Wk 01 (Prod)	Haul Trucks	Site	82.91%
2020	Thursday	4	Wk 02 (Prod)	Haul Trucks	Site	73.98%
2020	Friday	5	Wk 01 (Prod)	Haul Trucks	Site	81.96%
2020	Friday	5	Wk 02 (Prod)	Haul Trucks	Site	76.66%
2020	Saturday	6	Wk 01 (Prod)	Haul Trucks	Site	76.03%
2020	Saturday	6	Wk 02 (Prod)	Haul Trucks	Site	72.45%
2020	Sunday	7	Wk 01 (Prod)	Haul Trucks	Site	75.70%
2020	Sunday	7	Wk 02 (Prod)	Haul Trucks	Site	76.30%

Thanks!

[Icey]

Hi @jaydend ,

Please check whether the attached file is helpful.😀

Best regards

Icey

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[jaydend]

Hi @Icey , Unfortunately I am unable to open this file, potentially my work network is holding us at an older Power BI version and we can't manually update.  Is there another way i could view perhaps?

[Icey · Accepted solution]

Hi @jaydend ,

Let me explain in detail:

SQL Query table:

Date.Calendar Year	Date.Day Of Week	Week - Day	Date.Production Week Of Year	Asset Type	Location.Site	% HME Availability	Asset Type - modified	Asset Type - modified 2
2020	Monday	1	Wk 02 (Prod)	Haul Trucks	Site	90%	Haul Units	Haul Units
2020	Tuesday	2	Wk 02 (Prod)	Haul Trucks	Site	81%	Haul Units	Haul Units
2020	Wednesday	3	Wk 01 (Prod)	Haul Trucks	Site	80%	Haul Units	Haul Units
2020	Wednesday	3	Wk 02 (Prod)	Haul Trucks	Site	75%	Haul Units	Haul Units
2020	Thursday	4	Wk 01 (Prod)	Haul Trucks	Site	83%	Haul Units	Haul Units
2020	Thursday	4	Wk 02 (Prod)	Haul Trucks	Site	74%	Haul Units	Haul Units
2020	Friday	5	Wk 01 (Prod)	Haul Trucks	Site	82%	Haul Units	Haul Units
2020	Friday	5	Wk 02 (Prod)	Haul Trucks	Site	77%	Haul Units	Haul Units
2020	Saturday	6	Wk 01 (Prod)	Haul Trucks	Site	76%	Haul Units	Haul Units
2020	Saturday	6	Wk 02 (Prod)	Haul Trucks	Site	72%	Haul Units	Haul Units
2020	Sunday	7	Wk 01 (Prod)	Haul Trucks	Site	76%	Haul Units	Haul Units
2020	Sunday	7	Wk 02 (Prod)	Haul Trucks	Site	76%	Haul Units	Haul Units
2020	Sunday	1	Wk 01 (Prod)	Front End Loader	Site	76%	Dig Units	Dig Units
2020	Sunday	1	Wk 02 (Prod)	Shovel Excavator	Site	76%	Dig Units	Dig Units

Excel Query table:

Asset Fleet	each day of the week	target
Haul Units	Monday	90%
Haul Units	Tuesday	90%
Haul Units	Wednesday	85%
Haul Units	Thursday	90%
Haul Units	Friday	80%
Haul Units	Saturday	90%
Haul Units	Sunday	95%
Dig Units	Monday	90%
Dig Units	Tuesday	95%
Dig Units	Wednesday	90%
Dig Units	Thursday	75%
Dig Units	Friday	80%
Dig Units	Saturday	90%
Dig Units	Sunday	95%

1. For your first issue, it is suggested to create a conditional column in Power Query Editor or a calculated column in Data View.

Conditional column:

Calculated column:

Asset Type - modified 2 =
SWITCH (
    [Asset Type],
    "Haul Trucks", "Haul Units",
    "Front End Loader", "Dig Units",
    "Shovel Excavator", "Dig Units"
)

2. Create tables.

• Create a 'Day of Week Table' by entering data and sort 'Day of Week Table' by 'Order' column.

Day of Week Table	Order
Monday	1
Tuesday	2
Wednesday	3
Thursday	4
Friday	5
Saturday	6
Sunday	7

 

• You can also create a 'Asset Type Table' as above. Or, try this:

Asset Type Table = DISTINCT('SQL Query'[Asset Type - modified])

3. Create relationships.

4. Create a measure.

Measure = 
VAR Actual_ = SUM('SQL Query'[% HME Availability])
VAR Target_ = SUM('Excel Query'[target])
RETURN 
IF(Actual_>Target_,1,IF(Actual_<Target_,2))

5. Create a Matrix visual.

If there is any misunderstanding, please let me know.😉

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.