Forum Discussion
Updated data does not transform/load properly from folder
Aloha v-jingzhan-msft,
I opened the table. It does have data in there. However, I think that the transform sample file applied steps did not apply to the JUNE excel file. I opened the advanced editor for the transform sample file and the M code is to promote the headers for the source file.
Transform sample file M code:
let
Source = Csv.Document(Parameter1,[Delimiter=",", Columns=63, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers"
How would I be able to apply the transform sample file M code so it also applies to any new files I add in the folder?
Thanks!
Mark
Hi MPowerQ
The "Transform Sample File" code doesn't include any filtering steps, and you said "I opened the table. It does have data in there. ", so the transform sample file M code does have been applied to the new file (Step "Invoke Custom Function1" is to apply transform sample file code to all files). The reason may be in the next steps.
Please check the result after expanding the table column (5 - Applied step 6 (expand)). Scroll down or search by JUNE file name to check the data of JUNE file after expanding. If rows of JUNE file display blank or null at this step, this means the problem happens during the expanding process. When expanding tables in a column, it selects columns to expand based on their names in the sample file. So you can check that all the column names in the June file should be the same as those in the sample file. Any invisible leading or tailing space in columns may have an effect.
Best Regards,
Jing
Aloha Anonymous ,
You are correct, the transform sample file did apply to the June table; however, that table had an extra header that shows "sep=|." Right below that has the headers that need to be promoted. Not sure why this extra line is in there, but it is preventing the actual headers to be promoted and the June data to populate?
Thanks!
Mark
Aloha @v-jingzhan-msft ,
The contractor did a Microsoft TEAMS call and stated I had to create a Source Colum in the new data file. Somehow adding that column and naming it "9_JUN_24" allowed for the data to populate on the spreadsheet.
When using a folder connector, the data in all files to be combined in that folder should have the same schema. So for the new file, you will need to ensure that it has the same column headers as the sample file. As these files are CSV files, the delimiter should also be the same. Any additional row above the headers row does affect the result.