Forum Discussion

Krisso1975's avatar
Krisso1975
Regular Visitor
5 years ago
Solved

Merge not pulling through correct information

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 th...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

Krisso1975's avatar
Krisso1975
Regular Visitor
5 years ago

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's avatar
HotChilli
Community Champion
5 years ago

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?

 

  • HotChilli's avatar
    HotChilli
    Community Champion
    5 years ago

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

  • Krisso1975's avatar
    Krisso1975
    Regular Visitor
    5 years ago

    🙈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

  • Krisso1975's avatar
    Krisso1975
    Regular Visitor
    5 years ago

    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