How to convert list in a field into a relational table

[gk2go]

A have this table imported from CSV:

|| URL | Title | Tags ||

website.com/page-a | Page A | Tag-1; Tag-2

website.com/page-b | Page B | Tag-3; Tag-2

website.com/page-c | Page C | Tag-1; Tag-2

How can i convert it into the following?

1. Tags Table

|| Tag ||

Tag-1

Tag-2

Tag-3

2. Page-Tags Table

|| Page | Tag ||

website.com/page-a | Tag-1

website.com/page-a | Tag-2

website.com/page-b | Tag-3

website.com/page-b | Tag-2

website.com/page-c | Tag-1

website.com/page-c | Tag-2

[mahoneypat · Accepted solution]

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

[V-pazhen-msft · Accepted solution]

@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.

[mahoneypat · Accepted solution]

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