Problem
It is not rare to see that multiple attributes are stored in a single text field especially for tagging enabled applications where an unfixed number of tags may associated with an article or post. Those tags are often stored in a single text filed with a delimiter to separate them. When reporting, we often need to category the articles or posts by the tags, e.g., counting the articles or posts by each tag.
To fulfil this reporting requirement, We need to reshape our dataset from something like:
to something like:
Solution
It is actually very easy to conduct this kind of transformation using Power Query with only three lines of M code.
line 1 – split the tag field into unfixed number of tag columns using Splitter.SplitTextByDelimiter function.
line 2 – use Table.UnpivotOtherColumns function to unpivot all the tag columns. As we don’t have a fixed number of tag columns, we need to use UnpivotOtherColumns function and specify the known columns (“PostID”, “Title” in this example) as arguments.
line 3 – remove the column to store the generated tag column names which will not be used in reporting
Azure Data Ninjago & dqops 