Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello
I am trying to create some form of ranking, in PowerQuery, to help recognise if there is more than one funder allocated to a project. So in my visualisation(s) the client is then able to see that there is also a second(or more) funders involved in any particular project.
Table A, is my Key Project listing, this shows the 1st main contributor to that project, the 'REC_ID' is uniqure for each project which is also referenced by a 'BUDGET_NO'.
Table B gives goes into greater detail for each project, the table references the 'REC_ID', 'BUDGET_NO' and a unique 'BUDGET_LINE' reference, however there is no real indication how these rows are sorted. So while you think the first 'BUDGET_LINE' number is the first funder this is not always the case.
So, I want to turn this information in to one of two options. What would you suggest as the best route to take, what is best practice in these sitiations? And, how am I best to re-arrange this data to achieve this? -
Woud you suggest Option 1, Table C, add some form of ranking for each Funder.
or, Would you sugegst Option 2, re-arange the data like in Table D
Many thanks
Chris
Solved! Go to Solution.
Hi @ccarpent ,
unfortunately I cannot think of an elegant solution here, so you have to cope with this clumsy one:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY9LCsMwDESvYrzuojF2nCxFEpNQf0LstpSQA/VcvVgtCbIo3TyBNJqR9l02Sl6kMYZ5raUsIYsVljiVLcvj8qtpagmw3bIYEo01DrRpmWgxT/BIUfgykqC1tWe7nkkZEJ6pzGL1AynI23aaiYqQ4ggvzu9xUxnNxGku4Fw+1y0HKCYK3DaN9UBwn/cfBb4wpJjvvpwefVebjdJM+iLlpYCnN48v", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [REC_ID = _t, BUDGET_NO = _t, BUDGET_LINE = _t, FUNDER_NAME = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"REC_ID", Int64.Type}, {"BUDGET_NO", Int64.Type}, {"BUDGET_LINE", Int64.Type}, {"FUNDER_NAME", type text}}),
MergeToGetFirstRanks = Table.NestedJoin(#"Changed Type", {"REC_ID", "BUDGET_NO"}, TableA, {"REC_ID", "BUDGET_NO"}, "TableA", JoinKind.LeftOuter),
#"Expanded TableA" = Table.ExpandTableColumn(MergeToGetFirstRanks, "TableA", {"FUNDER_NAME"}, {"Main"}),
FilterNonFirstRanks = Table.SelectRows(#"Expanded TableA", each ([Main] <> [FUNDER_NAME])),
GroupNonFirstRanksAndApplyRanks = Table.Group(FilterNonFirstRanks, {"REC_ID", "BUDGET_NO"}, {{"All", each Table.AddIndexColumn(_, "Rank", 2,1)}}),
CombineNonFirstRanksWithFirstRanks = Table.Combine( GroupNonFirstRanksAndApplyRanks[All] ) & Table.SelectRows(#"Expanded TableA", each ([Main] = [FUNDER_NAME])),
ReplaceNullForFirstRanks = Table.ReplaceValue(CombineNonFirstRanksWithFirstRanks,null,1,Replacer.ReplaceValue,{"Rank"})
in
ReplaceNullForFirstRanks
This will handle any level of rank, therefore I second @Mariusz 's suggestion with the format.
... attaching file as well.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ccarpent
Probably the first option of ranking is better, however, there is a problem of ranking that remains.
What are your requirements ref to rank, is the Founder Name from Table A has to be always the first in the rank order?
Hi Mariusz
Thanks for reposnding.
Thats correct, Table 'A' dictates the main or key funder for that project or 'REC_ID', they will always be the first name based on that record.
Chris
Mariusz
Table A | |||
REC_ID | BUDGET_NO | FUNDER_NAME | |
12 | 555 | MARKS CO | |
45 | 456 | HEAVON LTD | |
67 | 789 | TAMWOTH PLC | |
55 | 784 | MONDAYS | |
99 | 254 | STAFFS PLC | |
77 | 782 | FREDS CAFÉ | |
98 | 124 | HOSITAL CO | |
Table B | |||
REC_ID | BUDGET_NO | BUDGET_LINE | FUNDER_NAME |
12 | 555 | 5550 | TIMS PAINETRS |
12 | 555 | 5551 | MARKS CO |
45 | 456 | 4560 | HEAVON LTD |
67 | 789 | 7890 | TAMWOTH PLC |
55 | 784 | 7840 | MONDAYS |
99 | 254 | 2540 | STAFFS PLC |
77 | 782 | 7820 | FREDS CAFÉ |
77 | 782 | 7821 | CONSULT PLC |
98 | 124 | 1240 | HOSITAL CO |
Is this ok?
Mariusz
Thats great thanks, I wil use this example on my model.
Chris
Hi @ccarpent ,
unfortunately I cannot think of an elegant solution here, so you have to cope with this clumsy one:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY9LCsMwDESvYrzuojF2nCxFEpNQf0LstpSQA/VcvVgtCbIo3TyBNJqR9l02Sl6kMYZ5raUsIYsVljiVLcvj8qtpagmw3bIYEo01DrRpmWgxT/BIUfgykqC1tWe7nkkZEJ6pzGL1AynI23aaiYqQ4ggvzu9xUxnNxGku4Fw+1y0HKCYK3DaN9UBwn/cfBb4wpJjvvpwefVebjdJM+iLlpYCnN48v", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [REC_ID = _t, BUDGET_NO = _t, BUDGET_LINE = _t, FUNDER_NAME = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"REC_ID", Int64.Type}, {"BUDGET_NO", Int64.Type}, {"BUDGET_LINE", Int64.Type}, {"FUNDER_NAME", type text}}),
MergeToGetFirstRanks = Table.NestedJoin(#"Changed Type", {"REC_ID", "BUDGET_NO"}, TableA, {"REC_ID", "BUDGET_NO"}, "TableA", JoinKind.LeftOuter),
#"Expanded TableA" = Table.ExpandTableColumn(MergeToGetFirstRanks, "TableA", {"FUNDER_NAME"}, {"Main"}),
FilterNonFirstRanks = Table.SelectRows(#"Expanded TableA", each ([Main] <> [FUNDER_NAME])),
GroupNonFirstRanksAndApplyRanks = Table.Group(FilterNonFirstRanks, {"REC_ID", "BUDGET_NO"}, {{"All", each Table.AddIndexColumn(_, "Rank", 2,1)}}),
CombineNonFirstRanksWithFirstRanks = Table.Combine( GroupNonFirstRanksAndApplyRanks[All] ) & Table.SelectRows(#"Expanded TableA", each ([Main] = [FUNDER_NAME])),
ReplaceNullForFirstRanks = Table.ReplaceValue(CombineNonFirstRanksWithFirstRanks,null,1,Replacer.ReplaceValue,{"Rank"})
in
ReplaceNullForFirstRanks
This will handle any level of rank, therefore I second @Mariusz 's suggestion with the format.
... attaching file as well.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Imke
Thanks for reply, I have accepted Mariusz solution but I will certenaily use both posts as reference point.
Thanks again for your help
Chris
If you have to do it in Power Query, @ImkeF might be able to assist.