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
dabutchart
Frequent Visitor

Lookup and return multiple Rows

I have a table with 2 columns NAME and LINE MANAGER NAME.

 

Using DAX, is it possible to return a new table showing who is line managed by each name, as in 

 

Source

Name Line Manager Name
John SmithClare Simpson
Sarah JonesAlice Johnson
Alice JohnsonClare Simpson
Peter WinterClare Simpson
Clare Simpson 

 

Output

 

NameNewColumn
John Smith 
Sarah Jones 
Alice JohnsonSarah Jones
Peter Winter 
Clare SimpsonJohn Smith
Clare SimpsonAlice Johnson
Clare SimpsonPeter Winter
1 ACCEPTED SOLUTION

Hi, @dabutchart 
as @PC2790 mentioned,
here's how to do it with Self join:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY0xCoAwDEWvEjL3EuJWVIQODqVDkGADNpXW+2MVHASnwHv//3iPEyUGNDiIMoyktHGBG2IwHm2OCi7JGVuk36kwOElHzfpoR4Ui2Kxcm+92WRnuyuu/5G9h5rP9W0Tb+Q18iUHAEC4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name ", type text}, {"Line Manager Name", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Name "}, #"Changed Type1", {"Line Manager Name"}, "Changed Type1", JoinKind.LeftOuter),
    #"Expanded Changed Type1" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type1", {"Name ", "Line Manager Name"}, {"Name .1", "Line Manager Name.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Changed Type1",{"Line Manager Name", "Line Manager Name.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Name .1", "New Column"}})
in
    #"Renamed Columns"

vojtechsima_0-1649753826391.png

 

View solution in original post

2 REPLIES 2
PC2790
Community Champion
Community Champion

Hey @dabutchart ,

 

You can make use of PATH functions to do this.

Here, refer this

Another way is to self join the table iwth itself and get the desired result.

LOOKUPVALUE can also be used depending on your requirement

Hi, @dabutchart 
as @PC2790 mentioned,
here's how to do it with Self join:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY0xCoAwDEWvEjL3EuJWVIQODqVDkGADNpXW+2MVHASnwHv//3iPEyUGNDiIMoyktHGBG2IwHm2OCi7JGVuk36kwOElHzfpoR4Ui2Kxcm+92WRnuyuu/5G9h5rP9W0Tb+Q18iUHAEC4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name ", type text}, {"Line Manager Name", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Name "}, #"Changed Type1", {"Line Manager Name"}, "Changed Type1", JoinKind.LeftOuter),
    #"Expanded Changed Type1" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type1", {"Name ", "Line Manager Name"}, {"Name .1", "Line Manager Name.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Changed Type1",{"Line Manager Name", "Line Manager Name.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Name .1", "New Column"}})
in
    #"Renamed Columns"

vojtechsima_0-1649753826391.png

 

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.