Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ccarpent
Helper IV
Helper IV

How to apply some form of ranking on my table

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 A, Key funder per projectTable A, Key funder per project

 

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.

 

All funders assigned to ProjectAll funders assigned to Project

 

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.

Table C, option 1Table C, option 1

 

or, Would you sugegst Option 2, re-arange the data like in Table D

Option 2, Table DOption 2, Table D

 

Many thanks

Chris

 

2 ACCEPTED SOLUTIONS

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

View solution in original post

Mariusz
Community Champion
Community Champion

HI @ccarpent 

 

Please see the attached file with both solutions.

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

View solution in original post

9 REPLIES 9
Mariusz
Community Champion
Community Champion

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?

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

 

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
Community Champion
Community Champion

Hi @ccarpent 

 

Can you paste both tables here ( just copy and paste from Excel )?

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

 

 

Mariusz

 

Table A   
REC_IDBUDGET_NOFUNDER_NAME 
12555MARKS CO 
45456HEAVON LTD 
67789TAMWOTH PLC 
55784MONDAYS 
99254STAFFS PLC 
77782FREDS CAFÉ 
98124HOSITAL CO 
    
Table B   
REC_IDBUDGET_NOBUDGET_LINEFUNDER_NAME
125555550TIMS PAINETRS
125555551MARKS CO
454564560HEAVON LTD
677897890TAMWOTH PLC
557847840MONDAYS
992542540STAFFS PLC
777827820FREDS CAFÉ
777827821CONSULT PLC
981241240HOSITAL CO

Is this ok?

Mariusz
Community Champion
Community Champion

HI @ccarpent 

 

Please see the attached file with both solutions.

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

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 

Greg_Deckler
Super User
Super User

If you have to do it in Power Query, @ImkeF might be able to assist.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors