Power BI M language - transform tag list into multiple rows

Question

Is it possible using query GUI or using M to create multiple output/target records based on analyzing source field? For example, if I have a source field as a list of tags separated by semicolons and have each tag as a single record in the target?

E.g. in the source:

"RecordX",  "Tag1; Tag2; Tag3; Tag4"
"RecordY",  "Tag2; Tag3; Tag5; Tag6"

In the target as separate records/lines:

"RecordX", "Tag1"
"RecordX", "Tag2"
"RecordX", "Tag3"
"RecordX", "Tag4"
"RecordY", "Tag2"
"RecordY", "Tag3"
"RecordY", "Tag5"
"RecordY", "Tag6"

Somebody in a similar case suggested the "unpivot" function. is it the solution for this? I need at least to understand terminology to look for the right functions, etc.

Answer 1

I'm assuming that your source data is like a JSON object or something?

Perhaps it looks like this:

{
    "RecordX":  "Tag1; Tag2; Tag3; Tag4",
    "RecordY":  "Tag2; Tag3; Tag5; Tag6"
}

And if that is the case, then I suggest you check this answer: Convert json List Record to Table value in PowerBI.

@Alexis is right, it is the 'Split Column By Delimiter' in to Rows which will solve this for you. See more details here and here.

For simplicity, here's a reproducible example:

let
    Source =
        "
        {
            ""RecordX"":  ""Tag1; Tag2; Tag3; Tag4"",
            ""RecordY"":  ""Tag2; Tag3; Tag5; Tag6""
        }
        ",
    ToJSON = Json.Document(Source),
    ToTable = Record.ToTable(ToJSON),
    SplitByDelimiter =
        Table.ExpandListColumn(
            Table.TransformColumns(
                ToTable,
                {
                    {
                        "Value",
                        Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv),
                        let
                            itemType = (type nullable text) meta [Serialized.Text = true]
                        in
                            type {itemType}
                    }
                }
            ),
            "Value"
        )
in
    SplitByDelimiter

Which has the result:

Name	Value
RecordX	Tag1
RecordX	Tag2
RecordX	Tag3
RecordX	Tag4
RecordY	Tag2
RecordY	Tag3
RecordY	Tag5
RecordY	Tag6