Access Query to Power BI Table Challenge

Question

Hello,&nbsp;I have a query and a table in Access. I have used a query to produce a simple resultant table.&nbsp;I have connected the query and table with their relationships to Power BI and I am trying to produce the same result in Power BI.&nbsp;I can produce the required result in Access, but not in Power BI.&nbsp;I have a Date table with the required relationship to the tables in Power BI. I have also tried a Disconnected Date Table, but it is not an ideal solution as there are other graphs on the same page which rely on the filters.I have tried multiple different approaches and have trolled through previous posts, but have gotten nowhere with this one.&nbsp;Below is the expected result&nbsp;Batch_ColourSumOfTonnes_Blast_idRomPad3_Finger_Gris1152RomPad3_Finger_Jaune2592RomPad3_Finger_Orange960&nbsp;&nbsp; The tables and relationships&nbsp;&nbsp;Table 1Date_TirageDate_TransfertBatch_nbTonnes_Blast_id2020-07-012020-07-01RomPad3_Finger_Orange_B000593362020-07-012020-07-01RomPad3_Finger_Orange_B000592882020-07-012020-07-02RomPad3_Finger_Violet_B000572882020-07-032020-07-03RomPad3_Finger_Gris_B000602882020-07-032020-07-03RomPad3_Finger_Jaune_B000621442020-07-032020-07-04RomPad3_Finger_Gris_B000603362020-07-042020-07-04RomPad3_Finger_Gris_B000602882020-07-042020-07-05RomPad3_Finger_Jaune_B000627682020-07-052020-07-05RomPad3_Finger_Gris_B000602402020-07-052020-07-05RomPad3_Finger_Jaune_B000623362020-07-052020-07-05RomPad3_Finger_BT_B000552402020-07-052020-07-06RomPad3_Finger_Jaune_B000627202020-07-052020-07-06RomPad3_Finger_Jaune_B000626242020-07-062020-07-07RomPad3_Finger_Orange_B000632882020-07-062020-07-07RomPad3_Finger_BT_B00055482020-07-062020-07-07RomPad3_Finger_Orange_B00063482020-07-062020-07-07RomPad3_Finger_BT_B000553362020-07-062020-07-07RomPad3_Finger_Orange_B000631922020-07-062020-07-07RomPad3_Finger_Violet_B000647682020-07-072020-07-07RomPad3_Finger_Orange_B000633842020-07-072020-07-08RomPad3_Finger_Orange_B000634802020-07-072020-07-08RomPad3_Finger_Orange_B000632882020-07-082020-07-09RomPad3_Finger_Orange_B000632402020-07-082020-07-09RomPad3_Finger_Violet_B000642882020-07-082020-07-09RomPad3_Finger_Violet_B0006411042020-07-092020-07-09RomPad3_Finger_Orange_B000631922020-07-092020-07-09RomPad3_Finger_Orange_B000631442020-07-092020-07-09RomPad3_Finger_Orange_B000631922020-07-092020-07-09RomPad3_Finger_Orange_B000631442020-07-092020-07-09RomPad3_Finger_Orange_B000632882020-07-092020-07-09RomPad3_Finger_Orange_B00063482020-07-092020-07-09RomPad3_Finger_Orange_B00063482020-07-09&nbsp;&nbsp;3362020-07-09&nbsp;&nbsp;2882020-07-09&nbsp;&nbsp;1442020-07-09&nbsp;&nbsp;288&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Table two&nbsp;Batch_nbOpen_DateClose_DateBatch_ColourRomPad3_Finger_Gris_B000522020-06-232020-06-25RomPad3_Finger_GrisRomPad3_Finger_Orange_B000532020-06-262020-06-29RomPad3_Finger_OrangeRomPad3_Finger_Jaune_B000542020-06-262020-06-29RomPad3_Finger_JauneRomPad3_Finger_BT_B000552020-06-27&nbsp;RomPad3_Finger_BTRomPad3_Finger_Stérile_B000562020-06-292020-07-02RomPad3_Finger_StérileRomPad3_Finger_Violet_B000572020-06-302020-07-03RomPad3_Finger_VioletRomPad3_Finger_Orange_B000592020-07-012020-07-06RomPad3_Finger_OrangeRomPad3_Finger_Gris_B000602020-07-032020-07-07RomPad3_Finger_GrisRomPad3_Finger_Jaune_B000622020-07-032020-07-07RomPad3_Finger_JauneRomPad3_Finger_Orange_B000632020-07-07&nbsp;RomPad3_Finger_OrangeRomPad3_Finger_Violet_B000642020-07-07&nbsp;RomPad3_Finger_Violet&nbsp;I am at a loss as how to do this. Any ideas?

mfelix · Answer

Hi&nbsp;Peter_au&nbsp;,
&nbsp;
In you table the last lines don't have any batch information.

However making use of your data I was abble to make the result crrect for the two top lines Jaune and Gris, as refered believe the difference is on the missing data.
&nbsp;
Think it has to do with the way you setup your slicers.
&nbsp;
Check PBIX file attach.

peter_au · Answer

Hi Miguel,&nbsp;I think it is getting close.&nbsp;The data in the table is missing because that is the reality of the input data. There is often no data for the last day and/or there may be missing dates in the table. That is why I need to have a date table attached to allow the user to chose any date available as the other graphics on the report are dependant on those consecutive dates.&nbsp;&nbsp;I was trying to avoid an independant date table as I would have to produce a new measure for every graphic on the page.&nbsp;CheersPeter&nbsp;&nbsp;

mfelix · Answer

Hi&nbsp;Peter_au&nbsp;,
&nbsp;
How do you know were the blank values go to?
&nbsp;
When you add the blank lines in the model since theres is no Batch you will get a line with blank and the quantity related with the date.

peter_au · Answer

The blank values mean there are no values on that particular date.The table will be blank for that date. Ideally it will look like this, or nothing in the tonnes column if there is no data.&nbsp;

mfelix · Answer

Hi&nbsp;Peter_au&nbsp;,
&nbsp;
So just to make it clear you want to have a single slicer for the date and then make the selection of filter according to this:

Open date &lt; Filter Date
Date Tirage &lt; Filter Date
Closed Date &gt; Filter date

Is that you question?
&nbsp;
If it is this you only option as you are refering is to have a date table because of two issues:

Two the dates are based on same table
You have different option depending on the date so this needs to be passed by a measure with variables

Forum Discussion

Access Query to Power BI Table Challenge

8 Replies

Related content

Power Query Grouping challenge

getting challenges with data science

Block access to power query

No CACULATE challenge

Matrix Challenges

Batch_Colour	SumOfTonnes_Blast_id
RomPad3_Finger_Gris	1152
RomPad3_Finger_Jaune	2592
RomPad3_Finger_Orange	960

Date_Tirage	Date_Transfert	Batch_nb	Tonnes_Blast_id
2020-07-01	2020-07-01	RomPad3_Finger_Orange_B00059	336
2020-07-01	2020-07-01	RomPad3_Finger_Orange_B00059	288
2020-07-01	2020-07-02	RomPad3_Finger_Violet_B00057	288
2020-07-03	2020-07-03	RomPad3_Finger_Gris_B00060	288
2020-07-03	2020-07-03	RomPad3_Finger_Jaune_B00062	144
2020-07-03	2020-07-04	RomPad3_Finger_Gris_B00060	336
2020-07-04	2020-07-04	RomPad3_Finger_Gris_B00060	288
2020-07-04	2020-07-05	RomPad3_Finger_Jaune_B00062	768
2020-07-05	2020-07-05	RomPad3_Finger_Gris_B00060	240
2020-07-05	2020-07-05	RomPad3_Finger_Jaune_B00062	336
2020-07-05	2020-07-05	RomPad3_Finger_BT_B00055	240
2020-07-05	2020-07-06	RomPad3_Finger_Jaune_B00062	720
2020-07-05	2020-07-06	RomPad3_Finger_Jaune_B00062	624
2020-07-06	2020-07-07	RomPad3_Finger_Orange_B00063	288
2020-07-06	2020-07-07	RomPad3_Finger_BT_B00055	48
2020-07-06	2020-07-07	RomPad3_Finger_Orange_B00063	48
2020-07-06	2020-07-07	RomPad3_Finger_BT_B00055	336
2020-07-06	2020-07-07	RomPad3_Finger_Orange_B00063	192
2020-07-06	2020-07-07	RomPad3_Finger_Violet_B00064	768
2020-07-07	2020-07-07	RomPad3_Finger_Orange_B00063	384
2020-07-07	2020-07-08	RomPad3_Finger_Orange_B00063	480
2020-07-07	2020-07-08	RomPad3_Finger_Orange_B00063	288
2020-07-08	2020-07-09	RomPad3_Finger_Orange_B00063	240
2020-07-08	2020-07-09	RomPad3_Finger_Violet_B00064	288
2020-07-08	2020-07-09	RomPad3_Finger_Violet_B00064	1104
2020-07-09	2020-07-09	RomPad3_Finger_Orange_B00063	192
2020-07-09	2020-07-09	RomPad3_Finger_Orange_B00063	144
2020-07-09	2020-07-09	RomPad3_Finger_Orange_B00063	192
2020-07-09	2020-07-09	RomPad3_Finger_Orange_B00063	144
2020-07-09	2020-07-09	RomPad3_Finger_Orange_B00063	288
2020-07-09	2020-07-09	RomPad3_Finger_Orange_B00063	48
2020-07-09	2020-07-09	RomPad3_Finger_Orange_B00063	48
2020-07-09			336
2020-07-09			288
2020-07-09			144
2020-07-09			288

Batch_nb	Open_Date	Close_Date	Batch_Colour
RomPad3_Finger_Gris_B00052	2020-06-23	2020-06-25	RomPad3_Finger_Gris
RomPad3_Finger_Orange_B00053	2020-06-26	2020-06-29	RomPad3_Finger_Orange
RomPad3_Finger_Jaune_B00054	2020-06-26	2020-06-29	RomPad3_Finger_Jaune
RomPad3_Finger_BT_B00055	2020-06-27		RomPad3_Finger_BT
RomPad3_Finger_Stérile_B00056	2020-06-29	2020-07-02	RomPad3_Finger_Stérile
RomPad3_Finger_Violet_B00057	2020-06-30	2020-07-03	RomPad3_Finger_Violet
RomPad3_Finger_Orange_B00059	2020-07-01	2020-07-06	RomPad3_Finger_Orange
RomPad3_Finger_Gris_B00060	2020-07-03	2020-07-07	RomPad3_Finger_Gris
RomPad3_Finger_Jaune_B00062	2020-07-03	2020-07-07	RomPad3_Finger_Jaune
RomPad3_Finger_Orange_B00063	2020-07-07		RomPad3_Finger_Orange
RomPad3_Finger_Violet_B00064	2020-07-07		RomPad3_Finger_Violet