Answered

Dataflows Not Populating Dataverse Lookup Fields

(0) Share

Report

Posted on by BenKraft

154

I'm looking to populate a Dataverse table (Inspections) with a few text columns and a lookup column to another table (Parts) from an excel file using a Dataflow.

The excel file contains all the text fields, as well as an extra text field that corresponds to the Part number. Within the "Parts" Dataverse table, the part number is the primary column.

To start, because I am importing from an excel sheet, I exported the Parts table and brought it into the file so that I could use the part's ID column (Oracle ID) I am using as the sole field for my custom Dataverse key (Oracle Key). My thought was that if I have the values used for the key, Dataverse would then be able to delineate whuch Part to use for the lookup.

Since I have the part name in the excel Inspections table, I use a query merge within Power Query to get the corresponding Oracle ID from the parts table. Because there are multiple inspections per part, there are repeated Oracle ID's, but all the part numbers were found, and there are no blank entries:

When selecting the column mapping, I mapped the fields and used the Oracle ID for the corresponding destination column as to plan:

However, after the flow finished, the resulting table had all the text-based fields populated but only a FEW correctly attributed parts; a majority of them were blank. Why might this be if all the Oracle ID's were found? I'm struggling to see a pattern.

Categories:

Power Query

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Sort by

Suggested answer

11manish 1,271 on at

Like (0)

Report
Copy link

Link copied!

Even though your Oracle ID values exist and are populated, Microsoft Dataverse does NOT resolve lookups based on arbitrary columns unless they are explicitly defined and matched correctly as alternate keys.

below might be helpful for you :

https://www.youtube.com/watch?v=m24yu-dwK8Q

Was this reply helpful? Yes No
BenKraft 154 on at

Like (0)

Report
Copy link

Link copied!

@11manish Thanks for the response!

As described, the Oracle ID is not an arbitrary column, it is the sole field used for the alternate key used by the Parts table:

When you say, "explicitly defined and matched", do you mean anything different than that? For clarity, my Parts table has a column that is populated by Oracle with an ID, I then "designate" that field to represent my key, then in my dataflow, I want to reference that record by using the corresponding Oracle ID, correct?

I went through the video you attached, and I have already completed the steps described and yet I get the same results, where there are many "missing" lookup entries.

Was this reply helpful? Yes No
BenKraft 154 on at

Like (0)

Report
Copy link

Link copied!

@11manish I've realized my problem is actually entirely unrelated!

Power Query is, upon import, doing some floating-point shenanigans:

This seems to happen UPON PoqerQuery import, as all the fields in the excel files are formatted as text:

The new question is how do I avoid these part numbers being turned into floats? In Excel they all appear as text and it doesn't seem to matter if I format the "Any Type" columns in PowerQuery back into text, they keep those trailing float values.

I added tables to Excel to see if it would consolidate the types to no avail:

(Excel)

(PowerQuery)

Was this reply helpful? Yes No
11manish 1,271 on at

Like (0)

Report
Copy link

Link copied!
try below :

Remove Changed Type

Immediately enforce:

= Table.TransformColumnTypes(Source, {{"PartNumber", type text}})

Then continue transformations

Was this reply helpful? Yes No
BenKraft 154 on at

Like (0)

Report
Copy link

Link copied!

@11manish Unfortunately this doesn't work. Again, transforming these floats simply turns values like 100.003200000001 into "100.003200000001". The conversion to floats occur DURING the original query

Was this reply helpful? Yes No
11manish 1,271 on at

Like (0)

Report
Copy link

Link copied!
Hi @BenKraf,

It seems that the problem is not the text conversion, it’s that the values are already corrupted when Power Query converts them to floating-point numbers.

we can try below :

1. Prevent numeric conversion entirely

Remove this step immediately:

Changed Type

This is where corruption starts

2. Force text at source step

Right after source:

= Table.TransformColumnTypes(Source, {{"YourColumn", type text}})

This ensures:

Value stays "100.0032"

No float conversion ever happens

3. If already converted → recover using rounding

If you cannot avoid conversion:

Number.Round([Column], 4)

Then:

Text.From(Number.Round([Column], 4))

Output:

100.0032

Was this reply helpful? Yes No
BenKraft 154 on at

Like (0)

Report
Copy link

Link copied!

@11manish Please stop using AI to answer questions. If you don't know a solution, I would much rather prefer no answers over ChatGPT-spewn ideas that do not work because it has not context of the issue. The numbers aren't "corrupted" and as I explained earlier, rounding doesn't work in this scenario since other part numbers contain text.

Was this reply helpful? Yes No
Verified answer

BenKraft 154 on at

Like (0)

Report
Copy link

Link copied!

I have come up with a semi-solution, which is to avoid the export-import process of bringing my Dataverse table to Excel then back into Power Query. Instead of two sheets in Excel, I use the data from the one sheet, and simply create another query to Dataverse directly. This resolves the strange type issues. I would still be curious to know why this happens.

Was this reply helpful? Yes No