Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 multipl...
Need Help
  • stretcharm's avatar
    stretcharm
    8 years ago

    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
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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.

stretcharm's avatar
stretcharm
Icon for Memorable Member rankMemorable Member
8 years ago

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.

 

 

  • stretcharm's avatar
    stretcharm
    Icon for Memorable Member rankMemorable Member
    8 years ago

    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