How to convert list in a field into a relational table

Starting with your input table and calling that query "RawData", you can use these two M queries to get your two tables.  Put the text over the default text of a blank query.  You can right click on the RawData and uncheck "Enable Load" to not load that into your model.

 

 

let
    Source = RawTable,
    #"Added Custom" = Table.AddColumn(Source, "TagsList", each Text.Split([Tags], "; ")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Tags", "Title"}),
    #"Expanded TagsList" = Table.ExpandListColumn(#"Removed Columns", "TagsList"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded TagsList",{{"URL", "Page"}, {"TagsList", "Tags"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Tags", type text}})
in
    #"Changed Type"


let
    Source = RawTable,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Tags"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Text.Split([Tags], "; ")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Custom",{"Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Custom", "Tags"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Tags", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type")
in
    #"Removed Duplicates"

 

 

Pat

 

gk2go 

Please follow the steps:

1. Split Tag column by "; " =>Make a copy of the table name it Table2

2. Remove Tags.2 in Table 1=>Remove Tags.1 in Table 2 => Rename the remaining Tags columns in both table as a same name e.g. "Tag".

3. Append Table 2 into Table 1 =>Right click Table 2 uncheck Enable load.

4. Right click the Tag column=>click "Add as new Query"=>Remove duplicates and convert to table. 

5. Close and Apply, add a relationship between Table 1 and Tag table.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.