I am trying to add a column that that adds the current row data and the next row data. Desired result is Month-Next Entry column.
Ultimately I need to convert customer Qtr to Calendar Qtr and the only way I can see to do this with data avail is to do this form of concatenate and then add a conditional column. I am open to better solutions…
Paste this into a new blank query and see how you get on with that.
The final value doesn’t match you expected result because there is no “next row”, I could code that BMO Plan in - if that makes sense for a final row - let me know.
let
lMonth = List.Buffer( Source[Month]),
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY+xCoAwDER/RTqboZgfqHQqtFXqVhzcxf8fTcRTUZdweXdwXK0mmtaMnZxIgUTMbTUBLPyyPuZmWJcNFn4kLn/qPTIiYSt14E4py3FUSISyAlYog2Ww/KjnTz1/6vlZz2d9Ak9KrZxEkrUH82Be19t7vX2tF2veAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Quarter = _t, #"Month-Next Entry" = _t]),
AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
AddCustom = Table.RemoveColumns( Table.AddColumn(AddIndex, "Custom", each Text.Combine( { [Month], lMonth{[Index]+1}?, [Quarter] }, "-" )), "Index" )
in
AddCustom
Welcome to the forum @wweber. It’s nice to see you participating
Did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
@Melissa Thank you for your reply, while this worked to create the table, I now see I have an issue capturing the correct year. Is there a simpler way to convert a fiscal M/Q/YR to a calendar M/Q/YR. Also need a way to roll the year as the new years come into view.
Just get the Basic Date table function from here and copy that full script into a New Blank Query.
.
Always invoke the Date table for full years (that can be either calender OR fiscal) and set your FYStartMonthNum to November to achieve the above result (at the Day level).
There’s also an ISO-8601 equavalent of this Date table called the Extended Date table, you can find that in the M Code Showcase category OR click on my Badge that has a direct link to that topic as well.
Hi @wweber, we’ve noticed that no response has been received from you since August 28.
We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
I had a little trouble establishing the relationship to pull this info in. I thought I can use any of the fields in the date table, but not sure how to tie them together. I can either get calendar year month to work or Fiscal year month to work but not both. In my regular date table (use the extended one) I am always successful getting whichever format I need, so I am confused and will likely try to load that one (assuming it is not the same as this one?)
A relationship with a proper Dates table is always at the lowest granularity - so at the daily level. At this point it’s unclear to me what you’re struggling with. Can you provide more context and a work in progress PBIX with mock up of the desired result.
Would love to provide better support but need more to go on…
It seems I needed to make a full date extension and loading the “Extended Date Table” both seemed to fix my issue, so I am all set now. Thank you so much for your support. This Customer Forecast conversion was a tough one, but I worked through it and feel so much more confident in my PBI Skillset.
Hi @wweber, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please create a new thread.
