Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 Smith | Clare Simpson |
Sarah Jones | Alice Johnson |
Alice Johnson | Clare Simpson |
Peter Winter | Clare Simpson |
Clare Simpson |
Output
Name | NewColumn |
John Smith | |
Sarah Jones | |
Alice Johnson | Sarah Jones |
Peter Winter | |
Clare Simpson | John Smith |
Clare Simpson | Alice Johnson |
Clare Simpson | Peter Winter |
Solved! Go to 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"
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"
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |