Missing cell values after LOOKUPVALUE in Table View

[WKN]

Data source in the form of .csv file with data as below table was used in Power BI Desktop.

Name	Date_Time	Temperature	Loop 1	Loop 2	Loop 3	Result
A	20230927152210	0	10	0	7831	-31.2
A	20230927152210	0	10	1	286240	-29.43
A	20230927152210	0	10	2	575073	-30.53
A	20230825190654	0	10	0	7831	-31.58
A	20230825190654	0	10	1	286240	-29.3
A	20230825190654	0	10	2	574973	-30.92

Data is split into 2 dataset with respect to Date_Time column. Delta to be calculated with reference to Result columns. 

New data table was generated in Power BI Desktop populating Post and Pre dataset with reference to later and earlier Date_Time.

SUMMARIZE and LOOKUPVALUE function was used. This is where missing cell value was observed. 

Missing cell value

Tried simulating with Delta Pre-Post instead and missing cell value appeared consistently.

Missing cell value

Another dataset was used to simulate the missing cell value, data source as tabulated below.

Name	Date_Time	Temperature	Loop 1	Loop 2	Loop 3	Result
B	20230823131534	0	10	0	10023	-30.79
B	20230823131534	0	10	1	346136	-35.93
B	20230823131534	0	10	2	693146	-32.76
B	20230920182531	0	10	0	10023	-30.69
B	20230920182531	0	10	1	346136	-35.75
B	20230920182531	0	10	2	693146	-33.26

Same steps applied and no missing cell value with this set of data source.

No missing cell value

My team and I have been loading the similar structured data source in .csv format to Power BI Desktop and today is the first time we discovered a missing cell value post processed within Power BI Desktop. Why is this occuring?

I would like to upload the .pbix file here but do not know how to do so.

[HotChilli · Accepted solution]

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.

[WKN]

Ok, I got it now. To date my team and I have never came across different Loop 3 values between the Pre and Post test data till now and thus assumed both will complete the same number of loops at all times. So we are not even aware when the Loop 3 data point is different but just fixated on the missing cell value and how it lead to error reporting. 😅

Your fresh pair of eyes have really helped in this simple error. We will discuss to decide if such data point in this scenario should be taken into consideration. Thanks for your time. 😇

[HotChilli]

Can 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

[WKN]

Ok 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. 

[HotChilli]

What are you expecting to happen here?

The loop3 value is different (574973 v 575073)

so the LOOKUPVALUE ->

Results_Pre = LOOKUPVALUE(Truncated_MissingPre[Result], Truncated_MissingPre[Name], 'Truncated_Missing_DeltaPost-Pre'[Name], Truncated_MissingPre[Loop 1], 'Truncated_Missing_DeltaPost-Pre'[Loop 1], Truncated_MissingPre[Loop 2], 'Truncated_Missing_DeltaPost-Pre'[Loop 2], Truncated_MissingPre[Loop 3], 'Truncated_Missing_DeltaPost-Pre'[Loop 3])

is working perfectly fine, it just won't find the data it's looking for because it's not present.

[WKN]

Hi, 

The LOOKUPVALUE is referenced to the Results column. No data is returned when data is present in the data source. 

[HotChilli]

Put it on a 3rd party site and post the link here.  I'll have a look later

[WKN]

Hi, 

The .pbix file and data source files (.csv) can be downloaded in below link. 

Missing Cell Values

Thanks and regards, 

WKN