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.

HotChilli's avatar
HotChilli
Community Champion
5 years ago

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's avatar
Krisso1975
Regular Visitor
5 years ago

I assume you mean this step

 

 

  • HotChilli's avatar
    HotChilli
    Community Champion
    5 years ago

    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's avatar
      Krisso1975
      Regular Visitor
      5 years ago

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

        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.