Forum Discussion

samroth's avatar
samroth
Microsoft Employee
6 years ago

Create column based off substrings from different table

I have two tables. The first is my main table with a bunch of information on items. One of the columns is an "Area Path". The following is a made-up example with similar information to what can be...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

This M code seems to work

let
    Source = Table.NestedJoin(Area_path, {"Text"}, Root_path, {"Text"}, "Root_path", JoinKind.LeftOuter),
    #"Expanded Root_path" = Table.ExpandTableColumn(Source, "Root_path", {"Text"}, {"Text.1"}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Expanded Root_path", "Text Before Delimiter", each Text.BeforeDelimiter([Text], "\", {0, RelativePosition.FromEnd}), type text),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Text Before Delimiter", {"Text Before Delimiter"}, Root_path, {"Text"}, "Root_path", JoinKind.LeftOuter),
    #"Expanded Root_path1" = Table.ExpandTableColumn(#"Merged Queries", "Root_path", {"Text"}, {"Text.2"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Root_path1", "Custom", each if [Text.1]=null then [Text.2] else [Text.1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Text.1", "Text Before Delimiter", "Text.2"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Text", Order.Ascending}})
in
    #"Sorted Rows"

Hope this helps.