Power Query returning "null" instead of date

ste4en

New member
Joined
Jul 24, 2018
Messages
13
Reaction score
0
Points
0
Excel Version(s)
2016
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
 
Can you post the workbook?
 
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
 

Attachments

  • Master Reconcilliation Worksheet.xlsx
    27.4 KB · Views: 15
  • ABC Urban 2019-2020 Cash and Expense workbook.xlsm
    71 KB · Views: 13
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
 
Last edited:
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
 
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
 
Last edited:
all good now thank you.
 
Back
Top