Merge not pulling through correct information

[Krisso1975]

I have couple of tables in power query, 1 for periods in the year and one showing billing data. I have created a column in the periods in the year that seems to be working that if today is greater than the start date but less than the end date of the month it produces the word current or previous. When I merge the tables using the end date for my merge it is showing previous on all the entries that should show current.

I have checked the table it is merging from and that is showing correct. Can anyone give me any guidence, I am very new to Power BI and using power query to manipulate data.

I do have an excel version of what I am trying to create in power bi but as you can imagine it uses a lot of formulas and runs very slow. Its also a lot less interactive than what I think a power bi verson would achieve.

Any help for a Power BI/Power Query newby will be greatfully received

Thanks

[v-alq-msft · Accepted solution]

Hi, @Krisso1975 

Based on your description, I assume that you want to create a custom column based on date column. I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a custom column with the following m codes.

let 
start = Date.AddDays( Date.EndOfMonth( Date.AddMonths([Date],-1)),1),
end = Date.EndOfMonth([Date]),
today = Date.From(DateTime.LocalNow())
in 
if today<start
then "Next"
else if today>end 
then "Previous"
else if today>=start and today<=end 
then "Current"
else null

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

I am sure we can help.

It sounds like the Merge is not set up correctly.

Can you show me some sample data and a picture of the Merge screen please?

[Krisso1975]

Hi HotChilli

Thank you for the help

This is the screen I have put the formula in to show current period

And the other table merges on Month End Date but is showing previous still even though the above is showing current

Thanks for your help

[HotChilli]

The first thing which is odd is that the datatype on the first Current Period screen is set to 'any', it should be text.

I would like to see the screen from the Merge step - the one where you pick the 2 tables and the join types please.

[Krisso1975]

I assume you mean this step

[HotChilli]

That's the screen.

I see that the tables join on PeriodEndDate and MonthEndDate and so we're looking for a MonthEndDate of 30/04/2021 in the splitBills table (if I've got that right) to bring back "Current".

Do we have that?

[Krisso1975]

I thought (wrongly) im guessing the merge step would acheive that on the above. You are correct on the return in the split bills table

To give you more context at what I am trying to acheive this is a screen grab of my excel version

So far I have easily created the annual and monthly targets and have pulled through accurate data for the years billing. I am now trying to acheive the current month data and then try and do the other calculations. If that makes sense

[HotChilli]

I can't really get in to the details of the end solution.

Do you want to carry on investigating the merge problem?  I was asking if there is a record with MonthEndDate of 30/04/2021 in the splitBills table.  If there is, have a look in the merged table to see what's happened to it.

If you want to post the 2 tables on a 3rd party site and the pbix, I'll take a look. Send the link thru this site message service if you don't want to post a public link. 

[Krisso1975]

The month end date of 30/04/2021 wont exist until next month when the Periods details table adds 12 new periods.

Would this be part of my issue though.

I cant really load the tables to anywhere as the contain client data and would breach our GDPR policy sadly

[HotChilli]

So that would explain it. The field doesn't come through in the merged table because there is no matching field in the splitBills table.  Does that make sense?

[Krisso1975]

🙈How did I not see that. The current should be the line above (01/03/2021 - 31/03/2021) Our system automatically creates a period 13 each year and this turns into period 1 when the new year is opened. So my question is now how do I fix the formula above to show current in the right place. I have just tried a few variations and none of those work

[HotChilli]

I'm out for the rest of the day, so sorry won't be able to reply.

[Krisso1975]

I possibly fixed it by not using a calculated coumn but a version of, if and, in a normal column.

Thank you for your help