Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
You can use the Merge operation with Left Outer Join to achieve what is desired.
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.
You can use the Merge operation with Left Outer Join to achieve what is desired.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |