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
jagostinhoCT
Post Partisan
Post Partisan

create alias table

Hello,

 

I am trying to create a dashboard using a User Group attendance list.

Attendees fill up a form where they type their role.

But each of them will type what they feel like so I get multiple entries such as "architect, Architect, Architectural, Designer", etc. which are far from being friendly when I want to analyse their backgrounds.

Roles Grouping.png

 

 

 

 

I am keen on grouping them into multiple categories, e.g. all for Designers, Management, Technical, etc so that I can get a clearer visual.

 

A couple of questions here.

 

What do you think is the best way to tackle this issue?

Or

How can I get the full roles list, as typed by the users, and add a second column so that I can type whatever I feel it is correct for each of the different entries?

 

Thank you

J

1 ACCEPTED SOLUTION

I've tweaked the fnVLookup to just do a text match search and pick the first entry by an order.

 

(lookup_value as any, table_array as table, col_index_number as number, optional array_order_column as number) as any =>
let

/*Provide optional sort column if user didn't */
sortColNo  =
if array_order_column  = null
then 0
else array_order_column - 1 ,

/*Get name of return column */
Cols = Table.ColumnNames(table_array),
ColTable = Table.FromList(Cols, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ColName_match = Record.Field(ColTable{0},"Column1"),
ColName_return = Record.Field(ColTable{col_index_number - 1},"Column1"),
ColName_Sort = Record.Field(ColTable{sortColNo}, "Column1"),

/*Find closest match */
SortData = Table.Sort(table_array,{{ColName_Sort, Order.Ascending}}),
RenameLookupCol = Table.RenameColumns(SortData ,{{ColName_match, "Lookup"}}),
Matches = Table.SelectRows(RenameLookupCol, each Text.Contains(lookup_value, [Lookup])),

Return = if Table.IsEmpty(Matches)=true
then "#N/A"
else Record.Field(Matches{0}, ColName_return)
in Return

View solution in original post

5 REPLIES 5
stretcharm
Memorable Member
Memorable Member

 

I would put a category field on your table

 

There a couple of ways to do this.

 

1) Create a manual mapping list. This is easy, but doesn't update automatically if they type bad data.

Then merge the mapping with your date lookup the category or create a relationship to the mapping as a lookup table.

 

2) Use keyworld lookup

 

I use this function from @KenPuls to do vlookup type keywork matches for SSIS package names to get a category in my SSIS dashboard.  http://community.powerbi.com/t5/Data-Stories-Gallery/SSIS-Catalog-DB-Dashboard/m-p/244677#M1110

Details of the function post is here.

https://www.excelguru.ca/blog/2015/01/28/creating-a-vlookup-function-in-power-query/

 

This is more flexible, but can still get it wrong especially if you have a keyworld that can have two possible categories. I use a rank to prioritse which keywords are matched first.

 

Also try to avoid pie/donuts as they are not good visuals.

Some great details picking the correct visual

https://www.youtube.com/watch?v=-tdkUYrzrio

https://www.sqlbi.com/wp-content/uploads/videotrainings/dashboarddesign/visuals-reference-may2017-A3...

https://www.sqlbi.com/p/power-bi-dashboard-design-course/

 

 

 

@stretcharm, thank you.

 

I think the vlookup use in Power BI is still too much for me to chew at the moment.

 

Maybe the manual table will suit me better, even considering all of it's caveats.

 

I managed to get the role column in the original datasource into another query and then create a (new) conditional column that does a bit of what I need.

 

The challenge, is obviously, if someone decides to type something else different I will need to go back and adjust manually add the conditional.

Thanks @KenPuls

 

It's an interesting and funny video. I didn't know what it was at the start. He's definately got a career in voice over work.

 

I'm doing string lookups but that works if the when the keyword is at the start. Now that I look again it's not doing what I thought as I was expecting it anywhere in the string. However all my test strings started with the keywords so everything worked.

 

I might have another go to either switch to the append method or rework your function to search in the string.

In my example it's reasonable to limit it to the start of the string as it's names of SSIS Packages and most people put the type at the front.

 

 

I've tweaked the fnVLookup to just do a text match search and pick the first entry by an order.

 

(lookup_value as any, table_array as table, col_index_number as number, optional array_order_column as number) as any =>
let

/*Provide optional sort column if user didn't */
sortColNo  =
if array_order_column  = null
then 0
else array_order_column - 1 ,

/*Get name of return column */
Cols = Table.ColumnNames(table_array),
ColTable = Table.FromList(Cols, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ColName_match = Record.Field(ColTable{0},"Column1"),
ColName_return = Record.Field(ColTable{col_index_number - 1},"Column1"),
ColName_Sort = Record.Field(ColTable{sortColNo}, "Column1"),

/*Find closest match */
SortData = Table.Sort(table_array,{{ColName_Sort, Order.Ascending}}),
RenameLookupCol = Table.RenameColumns(SortData ,{{ColName_match, "Lookup"}}),
Matches = Table.SelectRows(RenameLookupCol, each Text.Contains(lookup_value, [Lookup])),

Return = if Table.IsEmpty(Matches)=true
then "#N/A"
else Record.Field(Matches{0}, ColName_return)
in Return

Hey @stretcharm, thanks for the shout out, and glad you've found the VLOOKUP function useful.  It's recently come up that there is a way faster way to do this though.  Oz has a video on this here which you may want to consider:  https://www.youtube.com/watch?v=EYgKciBr_dg

 

I'm thinking it should run faster than my original approach.  🙂

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.