Forum Discussion
Max date for each day
Community SupportHi Anonymous
I don't understand what you want finally.
How does this look like:
another pivot table
Unique key
Max(rec_eff_dt)
Could you show an example of the output as the following said,
"for every table name on a particular day (we are using last 7 days from a separate date table as column headers) what was the max(rec_eff_dt)"
the screenshot make me puzzled.
Best Regards
Maggie
Hi Maggie,
These reports are built out in excel and I am trying to replicate these on PowerBI.
What excel has is:-
sheet Ingestion data:- which has data for multiple days for tables and when they were refreshed I am attching screenshots:-
step 1 - Ingestion data (table_name, rec_eff_dt, rec_create_dt)
step 2 Pivot ( concatenate(table_name,rec_eff_dt) --> Unique key , max(rec_eff_dt) step
3 Index match
In powerBI I
have the data - step 1
I created a group by - step 2 (something like pivot)
I have another table with just dates in it --> Date_list
Now for the step 3 index match I want the max(rec_eff_dt) for Date_list dates (last 7 days data) so the lookup I will be permorming is:- concatinate(Table_name, date_list) and look this value up in concatenate(Table_name,rec_eff_dt) (Pivot table) and list the corresponding max(rec_eff_dt) value
Step 1step 3step 2
- v-juanli-msft
Hi Anonymous
I make a test but i am stuck with some puzzle.
test with table you show in step1,
then group by, i get this
original table
table name rec rdl a 12/7/2018 12/19/2018 a 12/8/2018 12/19/2018 a 12/9/2018 12/19/2018 a 12/10/2018 12/19/2018 a 12/11/2018 12/19/2018 a 12/12/2018 12/19/2018 a 12/13/2018 12/19/2018 a 12/14/2018 12/19/2018 a 12/15/2018 12/19/2018 a 12/16/2018 12/19/2018 b 12/9/2018 12/19/2018 b 12/10/2018 12/19/2018 b 12/11/2018 12/19/2018 b 12/12/2018 12/19/2018 b 12/13/2018 12/19/2018 b 12/14/2018 12/19/2018 b 12/15/2018 12/19/2018 b 12/16/2018 12/19/2018 -> after group by
then i have a date table which just has a date column,
next i'm stucked, how do i connect the two tables?
in your step3 picture, "1/7/19~1/14/19" is from "date" table, dates in columns are max(rec_eff_dt) , right?
Best Regards
Maggie