Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Mark Wolven]

So, a few months ago I was asked to build some reports and the source of the data was going to be monthly Excel worksheets. A sheet is generated for each month and dropped in a folder organized by topic. So far, so good.

I used the Get Data|New Source|Folder option. Set the path, chose the 4 files that were there. Imported them and good to go.

Fast forward to now. There are 2 new additional files in the folder. Fine. Go to transform data, check the source, refresh the preview, the new files are on the list, and the new rows show in the preview table. Great.

I hit Close and Apply and it closes way too quick. The new rows aren’t visible in the table and the data is not up to date.

What am I missing? Is there a step I’ve forgotten?

——————————
Mark Wolven
Lead Informatics Consultant
Mark Wolven
8602733171
——————————

[Ben Howard]

Hi Mark, no magic answers but a couple of thoughts

1.  You say you choose the 4 files to load, you shouldn’t need to do this, you just do the folder.  Was this a typo or am I missing something?
2.  Any errors in the load.  When you initially created the query from Power BI there is a checkbox that says “Ignore files with errors” – do you remember if this was checked.
3.  Any filters on the report which wouldn’t display the data?

——————————
Ben Howard
——————————
——————————————-

[John Thomas]

Hi Mark. 

Sounds like instead of loading the files from the folder, it’s loading just one file.  (?) – This would generate the results you are describing.  New files are not loading, no errors, etc.  We do a lot of this type of loading as well.  I have found the following pattern helpful:
1. Create a function (or Parameter) that holds the path to your folders.  This way if something changes, it’s easy to modify in one place.
2. Perform the ETL functions needed on ONE file, then turn that query into a function.
3. When you load the files from folder, add a column From Function and use the function you created in step #2.

This should straighten things out.

If you are using Excel, here’s a function, fxGetParmater? that I use all the time.  If you are using Power BI, you can create a Parameters table and modify the code to pull data from the table in Power BI rather than the Excel Workbook table.

(ParameterName as text) =>
 let
     ParamSource = dimParameter,
//    ParamSource = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content],
     ParamRow = Table.SelectRows(ParamSource, each ([Parameter]=ParameterName)),
     Value =
	if Table.IsEmpty(ParamRow)=true
	then null
	else Record.Field(ParamRow{0},"Value")
in
    Value

The Table in Excel should have the following columns:

Parameter,      Value,                      Comments
Folder.Loc      C:MyDataExports   Folder Path to data

Name the Table “Parameters”.  You will need to remark out the dimParameter source in the above code and unremark the Excel.Workbook part of the code to switch.  If you are making a Power BI Table, it should have the same columns.

Modify your Source step to the following:
Source = Folder.Files(fxGetParameter(“Folder.Loc”))

Hope this helps.  If you need more specific help, upload a sample file so we can look at it…

Cheers!

——————————
John Thomas
Director of Project Controls and Estimating
Huntersville NC
980-287-2614
——————————
——————————————-