A D365 Finance and Operations reporting issue I had recently: How do I report on all published WBS activities by their related projects?
Well, the activities table (smmactivities) doesn’t have a project id field. If you are here for that specific question, the necessary table relationships are:
But I was interested in the process of doing this more than the specific reporting task. A basic way to do this could be to dump those tables into excel, then use a bunch of vlookups to add the associated project data to the activity rows.
Nothing wrong with that, but the final product would be a bit clunky…the whole smmActivities table, with a few extra columns. You could then delete or hide the unnecessary columns, but if you need to create the report again you’ll have to redo those same operations. If you need to add some attribute, you’ll have to do more vlookups (or xlookups, or index matches, whatever your preferred Excel operation). Is there a better way?
Introducing Power Query
Power Query is a tool to get and transform data in excel. In the example below, I exported the 4 desired tables, pointed to them as data sources, joined them together in Power Query, and then used Power Query to clean up my reporting output.
Here’s a video of that effort:
Reasons to Use Power Query
A Better Way to Get Data Power Query can connect to other tables in your excel file (like in my example above). Power Query can also be used to connect to other external data sources. In a more sophisticated reporting example, there is probably a way I could connect directly to a D365 data entity. I’ll look into that more in the future.
A Better Way to Transform Data I found Power Query to be a really pleasant experience for transforming data. A frequent D365 data migration issue is you’re given some legacy file, with some legacy file headers, which needs to be formatted into a D365 structure of columns and headers.
Instead of hacking away at the source data, which would be my first excel instinct. Power Query lets you add columns or rename headers programmatically…making it simple to redo the changes if you later get a NEW file of legacy data that also needs to be transformed.
I particularly like that Power Query shows a list of “applied steps”, which makes it easy to follow the transformations that take place between the original and transformed data.
My first-time user experience is that Power Query is a great tool, and I can see it becoming an integral part of improving D365 data migration projects.
The excel add-in connection is one of my favorite Dynamics features. What brings me more joy than updating a bunch of records through the excel add-in, or using the template to load a journal entry with tons of lines? Well, maybe hearing my son say “I love you too” brings me slightly more joy, but the excel add-in thing is a close second.
That said, sometimes I unintentionally close the excel add-in. Frustrated, I used to redownload my desired template from D365 and redo my work. This isn’t necessary though!
Here’s how you can get the excel add-in back:
Does anyone else frequently close this before they mean to?
A new parenting first: we took Calvin to the hospital today. When he was born three years ago, we took Calvin from the hospital. A nurse inspected the snugness of our rear-facing car seat, qualifying us to take a newborn home without any extra supervision. It felt like there should be more qualifications for taking a newborn home. We didn’t really know what we were doing. Does any new parent?
Twenty-some months later, we performed the ritual again taking his brother Lawrence home from the hospital. We had a slightly better idea what we were doing. All the car seat straps were in place before the nurse even reached our car.
And then there was today.
Luana called me outside, voice full of urgency, reminding me of the urgency in those previous hospital trips. Something was wrong.
She was carrying Calvin. Calvin was crying.
“He fell…at the playground. He crossed the bridge. He turned and lost his footing. He fell.”
I was in shock. “The wobbly bridge or the snake bridge?” I asked.
Luana’s expression changed from distressed to confused.
I continued, “The wobbly bridge is on the right. It shakes when Calvin crosses and he goes ‘WO-BBB-LY BRIDGE!!!’ The snake bridge is on the left. I stick my arms through the cargo netting and he shouts ‘Snakes!!!’ as he passes”
It was Luana’s turn to be shocked. “Does it matter?” she asked.
And she’s right. It doesn’t matter. What matters is that Calvin fell. What matters is that a five-foot fall is a long fall for a 2-and-a-half-foot body. What matters is that falling on brick is a hard fall for a body of any size.
“He fell on his arm,” she adds.
I gather Calvin’s crying body in my arms. A golf-ball lump of swelling bulges at his elbow. The arm is folded and doesn’t want to unfold. Calvin’s crying turns into screaming if I touch him anywhere below the shoulder.
Once, when I was 13, I flew over the nose of my skateboard and landed on my elbow. Gathering myself, I realized my left arm wouldn’t unbend. Holding the left arm with my right, I walked home and asked my mom to take me to the emergency room. The arm broke just above the elbow. Surgery followed, then a cast, then rehab. Two decades later, the arm has its full range of motion again, but the surgery scar on my elbow still makes a happy face if I curl the bicep.
That was the last time I skateboarded. Will Calvin ever skateboard?
I hold Calvin and he sobs. I don’t know what to do, but I’m trying to be calm because I don’t want Luana to think I don’t know what to do.
What can you do? You can’t make him unfall. I pray.
I pray, and I kiss Calvin’s head, and I tell him it will be ok. Will it be ok?
Is the arm broken? We need x-rays. We call a cab to the hospital and pile into the backseat when it arrives. Calvin falls asleep in my arms. At least he’s not crying anymore.
This is a good time to mention we’re in Brazil. We’ve been visiting Luana’s hometown for a few months. That’s why we’re in the back seat of a car without any carseats. I can feel the American hospital nurse from 3 years ago scowl disapprovingly into the future. Are we still qualified to be parents?
Brazil is also why the playground is built on brick instead of sand or mulch or that rubbery-tire stuff American playgrounds are built on. Would Calvin’s arm be ok if he had fallen in an American park?
We arrive at the hospital. It’s a white rectangle building with a church on one side: Hospital de Clínicas Nossa Senhora da Conceição.
Calvin still asleep in my arms, we walk into an empty reception area. Luana knows the woman at the front desk. This isn’t surprising. Luana’s hometown is small and Luana used to be a nurse. Luana knows almost everyone at the hospital.
The receptionist asks if we would like to pay to see a private doctor, or use public health. The question is disorienting. American hospitals never asked me that. I want the best medical care possible for my son. What’s the difference?
They’re the same, the receptionist explains, which leaves me more confused.
Luana helps move the decision along. We’ll use public health. The receptionist guides us back out the main hospital entrance, around the building, to a side entrance of the hospital. The side entrance is marked “public health”.
In a new empty reception area, we’re greeted by new receptionist friends who check us in.
Quick wait. Quick triage. Quick consult.
Everything in the hospital is very professional. Everyone’s uniform is pristine. The nurses and doctors smile when they see us. Their faces tighten sympathetically when they peer at Calvin sleeping in my arms and we explain what happened. They nod understandingly. X-rays are prescribed.
We sit in a waiting area while a technician prepares the x-ray room. Down the hall, I can see the reception area – the one we first entered, at the front of the hospital. It dawns on me how public and private healthcare can be the same. The difference is the door you enter.
The x-ray room is ready. Calvin wakes up as we enter.
It must be very alarming to be 3 years-old, fall asleep in the backseat of a car, and then wake up in an x-ray room for the first time. Especially when everyone in that x-ray room is wearing masks to prevent the spread of an invisible disease. Especially after you’ve fallen on your arm.
Calvin does not handle waking up well. He screams and cries again. I can’t tell if it’s more because of his elbow or our new location. I hold him in my lap, try placing his arm on the x-ray plate to take the image. He is having none of it and refuses to sit still. I try to calm him down.
“No need to be afraid. This is a big camera, just like your toy camera at home. Can we take a picture of your arm?”
“NO PICTURE. NO PICTURE! NO PICTURE!!!”
Calvin sobs and wiggles off my lap. He bolts for the door.
“OUT. OUT! OUT!!!” Calvin cries.
Calvin tries jiggling the door handle. He tries yanking it with all his little might. The heavy x-ray room door won’t budge. Finally, he hangs from the door handle and tries kicking off the wall.
The x-ray technician and I look at each other. Calvin is hanging from the door handle – supporting his whole weight – with the arm we are concerned might be broken. X-rays won’t be necessary. Calvin will be fine. Maybe he just needed some time and a nap.
Is this answered prayer? Is this our miracle? Is this the first patient cured by x-ray machine?
I think so. I don’t know.
Luana and I hug Calvin and comfort him and calm him down as we leave the hospital. We call a cab to go home.