Unable to append files with a conditional date column

Power Query
1

I have imported three worksheets from a single workbook. They each have an identical structure. I needed to transform dates from the IY Start Date column. If the date was before 01 Apr of a year the date is reset to 01 Apr of that year. Likewise if the date is after 31 Mar of the following year then the date is reset to 31 Mar of the following year. Dates in between are unchanged. I achieved this fine through Add Column/Conditional column in Power Query. I had to first replace null values with an actual date for the conditional column to work. I added this conditional column to each of the four imported worksheets. Before I added the conditional column, the append merge of all four files worked fine. However, after I added the conditional column and tried to append merge the files I got an error. So, I went back and changed the format of the M code for the conditional column so that it returned text and not a date then changed the type to date after the fact per below but it didn’t fix the error.

AddColumn
AddColumn718Ă—95 4.18 KB

Mergeerror
Mergeerror852Ă—143 5.12 KB

Replacenull
Replacenull727Ă—52 2.58 KB

Any help to resolve the error is greatly appreciated.

Thanks

Mark

2

Hi @Mombo,

Can you share a sample PBIX with a mockup file, to illustrate the issue?
Without a file it’ll be difficult to help you trouble shoot…

3

You should first clarify why you are talking about three sheets and then you upload four.

The type of error reported seems to indicate that one of the queries you are trying to merge is not a table but the string “#date (2018,04,01)” .
Try to check before you merge what the four queries you want to append contain

4

I went back and redid everything and it works now. Must have been a syntax issue.
Thanks.

Mark

1 Like