Refresh errors in data model

Power BI
1

Hi All,

I have just built a data model and normalized flat files while importing from a folder.
When I refresh the data it gives me the following errors:
Query ‘Customers’ (step Removed Duplicates) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

I have to click on multiple refreshes for the errors to go away. I have attached a screen shot and the file.
I have seen the videos of Normalization and have performed the same steps duplicating the query, but I get refresh errors.
Refresh errors
Sales.pbix (1.4 MB)

2

Hi @chris786 ,

While you are waiting, you could check Enteprise DNA Data Mentor Tools Advisor response:

1 Like
3

Hi @chris786,

You only appear to be referencing another query, in the Expanded Table step:
Table.ColumnNames(#“Transform File”(#“Sample File”))

This doesn’t look like dynamic content to me (but please correct me if I’m wrong), can you create a separate (hardcoded) list query, that will look something like:
{ “Name”, “Last date modified” }

And reference that (new list query name) instead?

4

Hi Melissa,
I didn’t get what you meant. I’m not very familiar with M code.
I am using the folder option here to import all the files, so that’s the reason its referring to the sample file, but I don’t get it , why does it cause a refresh error.
When, I go to the Power Query editor and refresh it works and then step back into Power BI, run a refresh it works. I cant figure out what am i doing wrong.

5

Could you share this file as well
C:\Users\Mustafa\Desktop\BI Data\Sales Data\

thanks

6

Hi Melissa,

I am attaching the folder with the files.
Thanks,
Sales Data.zip (7.9 MB)

7

Thanks for that @chris786 :+1:

I replaced hard coded folder locations with a FolderLocation parameter

And instead of this:
Table.ColumnNames(#“Transform File”(#“Sample File”))

Used this, for a dynamic expand.
Table.ColumnNames(#“Removed Other Columns1”{0}[Transform File])

Here’s your updated file:
Sales.pbix (1.4 MB)

Can you let me know if that resolves it for you?

8

Hi Melissa,

Could you please explain what the code does, does it pick up the first row from the Transform file.
I did try the file and it seems to work. I need to understand the workaround here.

Thank you, really appreciate it.

9

Hi @chris786,

Yeah, I’m using the combination item and field access to get the Headers from the table on the first row in the Transform File field.

Here’s an oldie, you may find useful otherwise check out the Nested Structures course within the Learning portal.

10

Thank much appreciated😊