Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have 2 tables, one is a table of projects that tell has a row telling me which way to spread the cashflow of the project (linear, backloaded, front loaded, etc.)
Table 1
Project ID Percent Complete Cashflow Type FORMULA TO RETURN %
123 .10 Backloaded .01
456 .20 Linear .20
789 .20 Frontloaded .21
The second table has the cashflow types as column labels and the rows tell me what %spend a project should be at (example below)
Table 2
Percent Complete Backloaded % Front Loaded% Linear%
.10 .01 .08 .10
.20 .03 .21 .20
I want to do a formula which I BELIEVE is a combination of LookupValue and Filter? Based on the cashflow type in the cell value from table 1 I would like to link to the correct column in table 2 and return the % complete from the correct column.
I will also add that the cell value in table 1 is not an exact match to the column name from table 2. I can update them to be exact if needed but I think the switch formula helps there?
Hi @Anonymous
See it all at work in the attached file.
This is best done in Power Query. Place the following M code in a blank query to see the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMtAzBJJOicnZOfmJKakpSrE60UompmZgKSMg6ZOZl5pYBBY2t7CEC7sV5eeVwLTEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectID = _t, PercentComplete = _t, CashflowType = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectID", Int64.Type}, {"PercentComplete", type number}, {"CashflowType", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"PercentComplete", "CashflowType"}, Table2, {"PercentComplete", "Type"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Value"}, {"Value"})
in
#"Expanded Table2"
and your table 2 needs to be unpivoted for easier transformation
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtAzVNIBkgZQygJMGSrF6oDkjCCCxmDKCKLESCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PercentComplete = _t, #"Backloaded%" = _t, #"Frontloaded%" = _t, #"Linear%" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PercentComplete", type number}, {"Backloaded%", type number}, {"Frontloaded%", type number}, {"Linear%", type number}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"PercentComplete"}, "Type", "Value"),
#"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Type", Order.Ascending}}),
#"Replaced Value" = Table.ReplaceValue(#"Sorted Rows","%","",Replacer.ReplaceText,{"Type"})
in
#"Replaced Value"
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
I ended up doing it by If's then lookupvalues pointing at the correct column. I have done something similiar to what you have above in PQ but it became really burdensome on the the functionality of the tool anytime I hit refresh. Thank you for your help
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
11 | |
9 | |
6 |