Challenge : how to generate a table spliting each row to new rows (in DAX !)

[Anonymous]

I have a Table1, which contains a list of categories (using | as separator)

Code	Categories
1	A|B|C
2	D|E

And I would like to generate a new table Table2 

Code	Category
1	A
1	B
1	C
2	D
2	E

I tried to use GENERATE / PATHITEM / GENERATESERIES functions, but it doesn't work : it seems that the PATH functions are always evaluated to BLANK when it is evaluated for each row of the Table1 inside the GENERATE function

New Table2 = GENERATE(
    Table1;
    var pathtext = Table1[Categories]
    var indexTable = GENERATESERIES(1;PATHLENGTH(pathtext))
    var categoryTable = GENERATE(indexTable;ROW("category";PATHITEM(pathtext;[Value];TEXT)))
    return categoryTable
)

Spoiler

 DAX errror : "The arguments in GenerateSeries function can not be blank"

Do you have any proposition to generate the table (with DAX) ?
Thanks,
Gregoire

Spoiler

[Anonymous · Accepted solution]

Here's the table:

Expansion = 
SELECTCOLUMNS(
    ADDCOLUMNS(
        GENERATE(
            Data,
            var __path = Data[Categories]
            return
                SELECTCOLUMNS(
                    GENERATESERIES(1, PATHLENGTH( __path ) ),
                    "@CategoryPosition", [Value]
                )
        ),
        "@Category", PATHITEM( Data[Categories], [@CategoryPosition] )
    ),
    "Code", Data[Code],
    "Category", [@Category]
)

Best

D

[Greg_Deckler]

I wrote a DAX UNPIVOT quick measure here: https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/m-p/574832#M256

Trick is going to be splitting out your data but figured it might help.

[Greg_Deckler]

I'll take a look for a DAX way to do this but it is super simple in Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs5PSVXSUXJOLElNzy/KTC1WitWJVjIECjnWONU4g3lGQJ5LjatSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Code", Int64.Type}, {"Categories", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Categories", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Categories.1", "Categories.2", "Categories.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Categories.1", type text}, {"Categories.2", type text}, {"Categories.3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Code"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns"

[Anonymous]

Hi @Greg_Deckler ,

thanks sincerely foryour 2d response in DAX which works perfectly !!! thank you.😀

But sorry to say that your 1st proposal with Query-M will not work when there is more than 3 categories in Table1[Categories].

With Query-M, a better solution can be realized in one step only, by splitting a column into rows  (and it is working with any growing number of categories in the data source, which is of course a requirement)

let
    Source = Table1,
    #"Split Column into rows" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Categories", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Categories")
in
    #"Split Column into rows"

Regards,

Gregoire