Forum Replies Created
-
Posts
-
Hi Tom,
I believe that for transfers, there is no reason it’s blank. So for transfers, the first two rows “Transfered” and to “BWH Coding – TA TRANSFER” should all fall under the first column Claim Action. The text string after Activity Time in the third column “now supports…” is the comment.
The “reason” row is not a show stopper for me if it would be easier to remove it so we have 3 rows for Tickled and 3 rows for a Transfer we can do that instead.
After you sent me the output from my first post I reviewed your steps and then went back to the full data set and removed the 5th blank row before adding it to Power Query.
I attached the original raw data that was not altered and labeled it “OriginalData_Not Altered” and labeled the sheet with the raw data and blanks that I removed as “OrignalData_No Blank Rows.”
In looking at the first sheet “Original Data_Not Altered” it appears that if the word “Tickled” is the value then every 5th row will have a blank. If the word “Transferred” is the value then every 3rd row will be a blank.
So it appears there will never be more than 5 rows per claim.
Hi Tom,
Thanks very much for your help that’s exactly how I need the final output. The original file I sent was only a portion of the full data set. I just tried recreating your steps with the full data set and I’m having a problem with the column group now because there are rows in the full data set that have a value other than “Tickled” for Column Group 1.
In the query editor the issue starts at Row 17. Power query is expecting the value in the second column to be “Tickled” and assigned it a value of 1 for the column group.
However there was an extra row of data to the entry above it in row 16 which threw off the numbering.
How can I adjust the Number.Mod to account for this?
I attached my updated file.
Thank you Tom. What a simple and effective solution.
I posted this same question on the form at PowerBI.com a few weeks ago because I wasn’t able to add a new message here, and you should have seen the overly complex suggestions I received.
This will work well for my situation.
Thanks again,
Brian
To clarify, I know that I can load this into PowerPivot and use CALCULATE but I’d like to aggregate these before I load the data into the data model.
Hi Tom,
I wanted to pick back up on this thread because i’m encountering a similar issue.
I have my transactions table and am trying to sum all of the rows in the related baseline table so that I can then write a measure to show Actual-Budgeted. I created the relationship between the two tables using the “Key” column and my Baseline measure is returning “blank”
The measure is written as: Baseline:=SUMX(ImportRadiologyRevenue, RELATED(BaselineRevenue[DailyExpectedRevenue])
Do you have any thoughts? I checked to make sure I was creating the relationship starting from my transactions table.
Your guess worked as designed! Thank you Tom.
Hi Tom,
Please see my data model that I’ve attached. I was able to create the relationship between the Baseline Revenue Table and my fact table using the ‘Key’ column. But now I’m having troubles displaying the budgeted against the actual.
Hi Tom,
I tried this suggestion and was able to create a custom column in my transaction table by doing the following:
Number.ToText([Cost Center ID) & “#” & Text.From(Cost Center) & “|” & Date.ToText(Post Date, “ddd”)
The column just about matches yours in the example except that the day of week abbreviation is not in all uppercase letters. Will that affect the lookup?
Sorry for the confusion, Tom. What I meant to ask is how could I take my budget table that is short and wide and convert that into a long and narrow budget table?
Thanks for the quick response Tom!
I understand what you are saying and I actually have that book!
The only question I have now is that my budget table won’t update at all throughout the year so it’s static. Those are the budgeted amounts for each day of the week so it won’t really be a fact table because i’m not adding any new rows to it.
Thank you
Hi Tom,
I’d like the fiscal year format to read “FY – 16” and fiscal quarter to read “Q3.”
Thank you for the feedback Steve! I tried this approach and it worked well. I also found a great article that mentioned your approach as well as another and I’ve posted it here:
I’m trying now to aggregate the procedure data and I want to be able to sum the transaction amount associated with each Account #, but with the table now splitting and adding additional rows for each procedure it’s summing everything. I’ve attached a sample of the new table.
I’m unsure if I should be using SUMX or CALCULATE. Could I write a CALCULATE measure that only looks at distinct values?
