Updated data does not transform/load properly from folder

[MPowerQ]

I have monthly data in a folder (Oct - May) that power query is pulling from.  However, when I added June's data to the folder, it did not format/display the data as it should have.  Example of the good data (Oct) and the data that did not load (June) is provided as well as the applied steps in the Power Query.  The data does not populate during applied step 6 if anyone can help troubleshoot.  Thanks!

1 - Oct data that displays properly

2 - Jun data that does not display

3 - Applied steps to the power query

4 - Applied Step 5 (remove columns)

5 - Applied step 6 (expand)

[Anonymous]

Hi @MPowerQ 

Please check if the table of JUNE in Applied Step 5 (remove columns) has content, you can click at space next to "Table" in the cell. 

If it doesn't have data for the file, please go to "Transform Sample File" query. In Home ribbon, open Advanced Editor, copy all its M code and paste it here (remove sensitive information). We'd like to check the M code to find if there is any step may do some filtering operation that might have some effect on the result. 

Best Regards,
Jing

[MPowerQ]

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

[Anonymous]

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

[MPowerQ]

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

[MPowerQ]

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. 

[Anonymous]

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. 

[adudani]

hi @MPowerQ ,

This solution for combining data may help:  (217) Combine Data from Multiple Excel Files | Most Dynamic Method (Dynamic Columns & Sheets) - YouT... 

if this doesn't resolve the issue, kindly provide the current error message, sample input and output data masking sensitive information.

Thanks

[MPowerQ]

Hello @adudani ,

Appreciate the reply and link to the video.

How the video setup combing multiple excel files was different than how the contractor setup our power query.  I removed the applied steps and am attempting to combine the data how the video shows, but immediately received the below error.  Was just trying to change the "Binary" fields to "Tables" on the content column as the video shows.  

Formula:

= Table.TransformColumns(
Source, {"Content", each Excel.Workbook(_)}