cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Jasel Regular Visitor
Regular Visitor

Add lookup data from another table/column using custom columns

While in the edit queries, I would like to add a custom column to an existing table which already contains the necessary ID's.

 

The table already looks like this (if this info is useful):

let
    Source = Sql.Database("BLAHDatabaseConnection", "BLAHDatabase"),
    STG_SA_timecard = Source{[Schema="STG",Item="SA_timecard"]}[Data],
    #"Replaced Value" = Table.ReplaceValue(STG_SA_timecard,"T"," ",Replacer.ReplaceText,{"Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Date", type datetime}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type","T"," ",Replacer.ReplaceText,{"DateSubmitted"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"DateSubmitted", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([TimeTypeID] <> "1" and [TimeTypeID] <> "12900")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"ProjectID", "TimeTypeID"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Other Columns", each true),
    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows1", "ProjectCategory", each if [TimeTypeID] = "12942" then "Non-Billable" else if [TimeTypeID] = "5" then "Non-Billable" else if [TimeTypeID] = "0" then "Billable" else null ),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column",{"ProjectCategory", "ProjectID", "TimeTypeID"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"TimeTypeID"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

I would like to take the ProjectID from 'STG SA_Billable', and use it to pull ProjectName from STG SA_Project.

I know that if I were to do this in DAX it would be: RELATED('STG SA_project'[ProjectName])

 

However I plan on appending the results with other tables to complete a very needed comprehensive list of specific ProjectNames so I need to do this using M or whatever language this is.

 

Documentation on M query, or this custom column formula language seems to be eluding me so if you could also link some helpful pages or DM them to avoid topic high jack I would appreciate it.

 

1 ACCEPTED SOLUTION

Accepted Solutions
BhaveshPatel Super Contributor
Super Contributor

Re: Add lookup data from another table/column using custom columns

You can use the Merge operation with Left Outer Join to achieve what is desired.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

2 REPLIES 2
BhaveshPatel Super Contributor
Super Contributor

Re: Add lookup data from another table/column using custom columns

You can use the Merge operation with Left Outer Join to achieve what is desired.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

Super User
Super User

Re: Add lookup data from another table/column using custom columns

You can use the UI to merge two tables and extract the column you need. Select merge then left outer join on the relevant matching column. From there you can extract the extra column you need. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)