Power Query returning "null" instead of date

[ste4en]

I am pulling several Excel Workbooks each with several worksheets into one power query. The first column is the date which I have formatted in all sorts of ways in the Excel worksheets with no change in the Power Query results. The date column contains "null" instead of the date. All of the other columns pull in fine (text & currency).

Any ideas,
Thanks

 

[Bob Phillips]

Can you post the workbook?

 

[ste4en]

workbooks and more explanation is here

I have attached two files - I have removed alot of the data for posting and provide just one data workbook to illustrate the error and what I am trying to do..

ABC Urban 2019-2020 Cash and Expense Workbook: This contains several tabs. I need to collect the data in the tabs with Exp, e.g. April exp, May exp, June exp, July exp

Master Reconcilliation Worksheet: From here I run the query. It collects the data from all of the Cash and Expense Workbooks a that exist in a folder.


In the Exp tabs the left hand column is the date which is not pulling into the reconcilliation workbook.


Thank you

 

[cyborgski]

Try setting your Add Column to false in the line below and then rebuild the rest of your query. Your column names will be consistent across sheets and the dates should show up.
Table.AddColumn(#"Filtered Rows2", "sheets", each Excel.Workbook([Content],false))

Hope this helps,
Mike

 

[ste4en]

Table.AddColumn(#"Filtered Rows2", "sheets", each Excel.Workbook([Content],false))

Hello Mike, Thanks for having a look.

I am not sure where to put your line. Can you be more specific. I tied to add it in the Custom Column formula box but I get an error that "The name 'Filtered Rows2' wasn't recognized". So im doing something wrong. Can you point me right..Thanks

Steve

 

[cyborgski]

Sorry Steve, I had other files in the directory that I had saved to and forgot that I filtered. The line below would go after your Source step.

= Table.AddColumn(Source, "sheets", each Excel.Workbook([Content],false))

Note that this will change the column names of your workbook tables coming in to {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}. You will need to change your Table.ExpandTableColumn step to accommodate the change.

Regards,
Mike

 

[ste4en]

all good now thank you.