Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
)
Solved! Go to 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
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
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.
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"
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
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |