Forum Discussion
Compare Dates, Hour, Minute between two Data set and return Value
Hello,
I have two set of data as below.
My need to compare the Reference (Date, Hour, Minute) with Actual Source Data (Date, Hour, Minute) and returns the Value if Date, Hour, Minute is matched.
Basically, the operation will have to happen like below and go on:
I tried doing this with Loop and If Condition, but I couldn’t succeed. Can anyone please help or provide me a lead to make this happen!! Looking forward!! Thank you!!!
Hi Anonymous
I get a result table as below ( filter [Hour] <=5 and [Date] =2019/1/1, so that it displays more clearly)
If it meets your requirement, please refer to the steps below
1. In Edit queries, in "Date" table, Add two custom column
Custom=#"Hour"
Custom.1=#"Minute"
then expand two custom columns
2.
In "Date" table,
select "Date", 'Custom.Hour", "Custom.1.Minute" , then "Add column"->Merge column(Separator is "-" in my example)
Then in "Actual Source Table",
select "Date", 'Hour", "Minute" , then "Add column"->Merge column(Separator is "-" too)
3.
In "Date" table, merge queries, then expand "Merged 2" column from "Source table"
4. Close&&apply, create a cacluated column
count = CALCULATE(COUNTROWS('Date'),FILTER(ALLEXCEPT('Date','Date'[Date]),[Actual Source Table.Merged2]<>BLANK()))Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Check out the function LOOKUPVALUE, that should be all you need. I do not see a need for looping here.
Thanks for the HeadsUp!!!
Actually I need to compare the Date, Hour & Minute and get the Value and count as well. Like how much value for Date 1/1/2019, 1/2/2019 and so on. It doesn't end up with 3 Dates. It may keep going for a year!! That's why i thought of Loop and If condition.
But I had an idea like below:
Output = Count of (Lookup( Reference Value, Reference Date = Source Date, Reference Hour = Source Hour, Reference Minute = Source Minute, "0", "1"))
Please let me know if the above concept works!! First it will look up the Date,Hour,Minute and then return a Value.
That Value will be counted as I am using Count function.
Hi Anonymous
I get a result table as below ( filter [Hour] <=5 and [Date] =2019/1/1, so that it displays more clearly)
If it meets your requirement, please refer to the steps below
1. In Edit queries, in "Date" table, Add two custom column
Custom=#"Hour"
Custom.1=#"Minute"
then expand two custom columns
2.
In "Date" table,
select "Date", 'Custom.Hour", "Custom.1.Minute" , then "Add column"->Merge column(Separator is "-" in my example)
Then in "Actual Source Table",
select "Date", 'Hour", "Minute" , then "Add column"->Merge column(Separator is "-" too)
3.
In "Date" table, merge queries, then expand "Merged 2" column from "Source table"
4. Close&&apply, create a cacluated column
count = CALCULATE(COUNTROWS('Date'),FILTER(ALLEXCEPT('Date','Date'[Date]),[Actual Source Table.Merged2]<>BLANK()))Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Anonymous
Is this problem sloved?If it is sloved, could you kindly accept it as a solution to close this case?If not, please let me know.Best RegardsMaggie
