@melissa as requested new thread. I have another table which I have added called EmployeeclusterTeams and basically in Tktable I want to add a column next to it called “Cluster” based on the transdate. So I want to be able to look at the transdate in Tktable and compare it to “CustPreviousteamstartdate” and “CustPreviousteamenddate” in the EmployeeClusterTeamstable. If the transdate is between these two dates I want to return the “Original Cluster” column in EmployeeClusterTeams if not I want to return the “Current Team”.
Here is a screenshot of my expected result for Employee 253
Thanks.
Here’s the logic for the Cluster column in the TkTable
Table.RemoveColumns(
Table.AddColumn( Cluster, "Cluster",
each
let
tDate = [TransDate],
nTable = if [AllRows] = null then 0 else Table.SelectRows( [AllRows], (IT) => IT[CustPreviousteamstartdate] >= tDate and IT[CustPreviousteamenddate] <= tDate),
nRows = if [AllRows] = null then null else Table.RowCount(nTable)
in
if nRows = null then null
else if nRows = 0 then [AllRows]{0}[Current Team]
else [AllRows]{0}[Original Cluster],
type text
),
"AllRows"
)
Hi @Melissa thanks for this. This is really power query on steroids. I don’t think I will ever be able to come up with a solution like this. I am just still digesting and trying to understand. I know you’ve got a power query course in the membership. Will that take me to this level you are at as I am a lifetime member? Crazy good stuff @Melissa. Really impressive!!!
It certainly has that potential IMO. For the most part, it covers important concepts in M. These have been instrumental in my own development and learning but ultimately - it’s up to you…
When you have worked through it, would you be so kind to fill out the survey? I’d greatly appreciate it! And that will be incredibly helpful to me, developing subsequent courses for eDNA.
LOL, no. Trust me, I’ve tried… no more than taking intensive voice lessons will make you Pavoratti.
However, the course is phenomenal and I cannot recommend it highly enough. I have taken it twice all the way through and watched some of the modules 3 or 4 times. If you really study it and then practice, you will be amazed at how quickly you will progress toward an advanced level. In just a month’s time, I am doing things in M and PQ in my forum solutions and my own reports that I would not have been able to do prior, and my overall understanding has increased dramatically.
@BrianJ thanks for the tips. You guys are releasing so many courses I just can’t keep up. Juggling studying with work and also family is tough to find time around but will keep at it. I want to also start taking part in the challenges. I have not yet attempted even one yet. I am just don’t feel confident yet especially when it comes to the visualisations, bookmarks etc. I need to cover the advanced visualisation course as well for @MudassirAli . Crazy but you guys are doing fantastic work bringing in new content and rolling them out fast. True leaders and pioneers.
Thanks so much for the positive feedback. I would really encourage you and everyone else just to jump into the Challenges as soon as possible. If you wait until you feel comfortable with every technique you’ll never join in, and the absolute best way to learn some of these skills is just to try implementing them in your challenge entry. The challenges are an awesome place to experiement and learn, trying things that you might be hesitant to do in your real-world reports. I guarantee that if you do, you will progess faster than you ever imagined, and have a great time in the process.
Hope to see you on the “newcomer” list in the next Challenge (coming soon!)
Just don’t do it all at once. Pick a course to start with, create a study schedule you can keep and like @BrianJ already suggested, no matter where you start there’s absolute no substitude for practice, so make participating in any of past or present eDNA events like: Problem of the week, Accelerator or Challenges a priority.
If you find the Challenges a bit daunting, break them down, choose a focus erea, so any of the four Power BI development pillars to pay special attention to, that round like:
Dataprep (Power Query)
Data modelling
DAX Calculations
Visualizations
You’ll be amazed by the amount of progress you’ll make
Hope to see you in any of the Challenges.
Hi @ambepat, did the response provided by the users and experts 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.
