I have a table that has there columns that I will be using:-
rec_eff_dt Date that table was loaded
Valid date If rec_eff_dt <= Today then 1 else 0
Unique key Table_name&rec_eff_dt&Valid date ----> abc12/10/20181 (This is what the unique key looks like)
I have created another pivot table
I want to show in a table:-
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)
In excel they are performing and index match where they choose the table name, the date from column header and 1 (i.e table name& A1(say its 1/7/19)&"1" to create a custom unique key with the column header date and looking this value up in pivot table to find the corresponding max(rec_eff_dt) and populating that in the matrix.
How can I accomplish this in PowerBI. How do I perform a dynamic lookup by concatenating the table_name, column header date and 1?
I don't understand what you want finally.
How does this look like:
another pivot table
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.
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
I make a test but i am stuck with some puzzle.
test with table you show in step1,
then group by, i get this
-> 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?