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
Jasel
Helper II
Helper II

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
BhaveshPatel
Community Champion
Community Champion

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

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
BhaveshPatel
Community Champion
Community Champion

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.

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.