cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ccarpent Helper I
Helper I

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'.

 

Key Funder Table A.PNGTable 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 Funder Table b.PNGAll 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.

option 1 table c.PNGTable C, option 1

 

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

option 2 table d.PNGOption 2, Table D

 

Many thanks

Chris

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

Re: How to apply some form of ranking on my table

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.

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

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

Highlighted
Super User IV
Super User IV

Re: How to apply some form of ranking on my table

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
Super User IV
Super User IV

Re: How to apply some form of ranking on my table

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


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User IV
Super User IV

Re: How to apply some form of ranking on my table

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

 

 

ccarpent Helper I
Helper I

Re: How to apply some form of ranking on my table

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

Super User IV
Super User IV

Re: How to apply some form of ranking on my table

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

 

 

 

ccarpent Helper I
Helper I

Re: How to apply some form of ranking on my table

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?

Super User III
Super User III

Re: How to apply some form of ranking on my table

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.

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

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

Highlighted
Super User IV
Super User IV

Re: How to apply some form of ranking on my table

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

ccarpent Helper I
Helper I

Re: How to apply some form of ranking on my table

Mariusz

 

Thats great thanks, I wil use this example on my model.

 

Chris

ccarpent Helper I
Helper I

Re: How to apply some form of ranking on my table

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 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors