Forum Discussion
Helper IMissing cell values after LOOKUPVALUE in Table View
Thank you for the clarification. It's a significant effort and I appreciate it.
I am sticking to my analysis of the problem which I posted above. The LOOKUPVALUE is not going to find anything because the data it is looking for does NOT exist. Examine the parameters which are passed to the function. Now compare with the data in the table. The row which you expect to match has a different loop3 value. So it will never find anything in the search table.
There are 2 possibilities : 1: I am not explaining my point about the LOOKUPVALUE very well
2: Your understanding of LOOKUPVALUE is not how it really works
----
There is a significant difference in the dataset where everything works ok and the one where it doesn't. For the working dataset, there are effectively 2 sets of 3 rows which have the same key values.
The dataset which does not work has 2 sets of 3 rows too. However, they do not have the same key values (Name, loop1, loop2, loop3)
Have a think and let me know.
Community ChampionCan you explain that in more detail please? i.e. with the exact values you think are being passed to the LOOKUPVALUE and the values in the search table that it is looking for
WKNOk here is the detailed explanation. I upload data source file Truncated_Missing.csv into Power BI Desktop.
Data source file Truncated_Missing.csv
In Power Query, split the data into 2 dataset.
1. Truncated_MissingPost - data filtered to latest Date_Time 20230927152210
2. Truncated _MissingPre - data filtered to earliest Date_Time 20230825190654
Truncated_MissingPostTruncated _MissingPre
Created new table, Truncated_Missing_DeltaPost-Pre using SUMMARIZE function to populate columns from Truncated_MissingPost.
Columns Name, Temperature, Loop 1, Loop 2, Loop3 and Result is populated correctly from Truncated_MissingPost.
Renamed Results to Results_Post.
Truncated_Missing_DeltaPost-Pre
Use LOOKUPVALUE to lookup Result column from Truncated_MissingPre (referenced as column Results_Pre) to match search condition of Name, Temperature, Loop 1, Loop 2 and Loop3 of summarized Truncated_MissingPost dataset. Now the lookup value of -30.92 from Truncated_MissingPre is not showing up in Truncated_Missing_DeltaPost-Pre dataset.
Truncated_Missing_DeltaPost-Pre lookup resulting in missing cell value
Missing cell value will cause inaccurate calculated value of Delta_Post-Pre = Result_Post - Results_Pre.
The above steps were reversed, by summarizing from Truncated_MissingPre then lookupvalue from Truncated_MissingPost.
Error is reproducible once more whereby lookup value of -30.53 from Truncated_MissingPost is not showing up in Truncated_Missing_DeltaPrePost dataset.
Truncated_Missing_DeltaPre-Post with missing cell value after lookup operation.
Missing cell value resulted wrongly calculated value of Delta_Pre-Post = Result_Post - Results_Pre.
I performed further experiment by using a different set of datasource, file Truncated_Present.csv.
Truncated_Present.csv
Performed the similar steps to split the data to 2 dataset with latest and earliest Date_Time in Power Query.
These are tables Truncated_PresentPost and Truncated_PresentPre. Truncated_PresentPostTruncated_PresentPre
Truncated_Present_DeltaPost-Pre is summarized from Truncated_PresentPost then lookupvalue Result in Truncated_PresentPre.
The lookupvalue is present with this data source!
Truncated_Present_DeltaPost-Pre without missing cell value after lookupvalue operation.
Why is there such descrepancies between the use of these 2 datasource (Truncated_Missing.csv and Truncated_Present.csv - actual files also shared in link) upon loading in Power BI Desktop? Please note that these data were truncated from a larger set of data source.
We only managed to capture this error upon performing data review from visuals created from further data processing of these data source using Power BI Desktop. It will be increasingly tedious for us to keep looking out for these missing cell value errors.