Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I have a list of product names with 1-3 keywords and item quantities. Would it be possible to create a matrix with different keyword search results?
Here is a data sample:
Product Name | Value |
A-B | 21 |
A-B-C | 37 |
B-C | 45 |
C-D | 56 |
D | 18 |
Category | Value |
A | 48 |
B | 103 |
C | 138 |
D | 74 |
Thank you for the support!
Solved! Go to Solution.
Hi, @taiyaki35 ;
Through your example, I used two methods, one is dax, the other is powerquery, as folllows:
Method 1: Create a new table
Newtable=
VAR _A=SUMMARIZE('Table',"Category","A","Value",CALCULATE(SUM('Table'[Value]),FILTER('Table',CONTAINSSTRINGEXACT([Product Name] ,"A"))))
VAR _B=SUMMARIZE('Table',"Category","B","Value",CALCULATE(SUM('Table'[Value]),FILTER('Table',CONTAINSSTRINGEXACT([Product Name] ,"B"))))
VAR _C=SUMMARIZE('Table',"Category","C","Value",CALCULATE(SUM('Table'[Value]),FILTER('Table',CONTAINSSTRINGEXACT([Product Name] ,"C"))))
VAR _D=SUMMARIZE('Table',"Category","D","Value",CALCULATE(SUM('Table'[Value]),FILTER('Table',CONTAINSSTRINGEXACT([Product Name] ,"D"))))
RETURN UNION(_A,_B,_C,_D)
The final output is shown below:
Method 2: split and unpivot in power query
a)split column
b)unpivot columns
c)group by column
The final output is shown below:
In addition ,here is M language:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctR1UtJRMjJUitUBc3SdgVxjczAXwjExBXOcdV2AHFMzMAfENLRQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Name" = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Name", type text}, {"Value", Int64.Type}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Product Name", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Product Name.1", "Product Name.2", "Product Name.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Product Name.1", type text}, {"Product Name.2", type text}, {"Product Name.3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Value"}, "Attribute", "Value.1"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Value.1"}, {{"Sumvalue", each List.Sum([Value]), type nullable number}}),
#"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Value.1", "Category"}})
in
#"Renamed Columns"
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @taiyaki35 ;
Through your example, I used two methods, one is dax, the other is powerquery, as folllows:
Method 1: Create a new table
Newtable=
VAR _A=SUMMARIZE('Table',"Category","A","Value",CALCULATE(SUM('Table'[Value]),FILTER('Table',CONTAINSSTRINGEXACT([Product Name] ,"A"))))
VAR _B=SUMMARIZE('Table',"Category","B","Value",CALCULATE(SUM('Table'[Value]),FILTER('Table',CONTAINSSTRINGEXACT([Product Name] ,"B"))))
VAR _C=SUMMARIZE('Table',"Category","C","Value",CALCULATE(SUM('Table'[Value]),FILTER('Table',CONTAINSSTRINGEXACT([Product Name] ,"C"))))
VAR _D=SUMMARIZE('Table',"Category","D","Value",CALCULATE(SUM('Table'[Value]),FILTER('Table',CONTAINSSTRINGEXACT([Product Name] ,"D"))))
RETURN UNION(_A,_B,_C,_D)
The final output is shown below:
Method 2: split and unpivot in power query
a)split column
b)unpivot columns
c)group by column
The final output is shown below:
In addition ,here is M language:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctR1UtJRMjJUitUBc3SdgVxjczAXwjExBXOcdV2AHFMzMAfENLRQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Name" = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Name", type text}, {"Value", Int64.Type}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Product Name", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Product Name.1", "Product Name.2", "Product Name.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Product Name.1", type text}, {"Product Name.2", type text}, {"Product Name.3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Value"}, "Attribute", "Value.1"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Value.1"}, {{"Sumvalue", each List.Sum([Value]), type nullable number}}),
#"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Value.1", "Category"}})
in
#"Renamed Columns"
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @parry2k , Got it. I will try figuring out the partterns and fix the data. Thank you!
@taiyaki35 well, that is the only way to do the scalable solution. You have to find the pattern and fix your sku. I wouldn't recommend monkeying around with some DAX code to solve the problem. It is better to find a way to fix your data rather than some other crazy solution. Just my 2 cents.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @parry2k , thank you for the reply! I did consider spliting product column in rows. But because the strings of product names contained other irregular information at differnt positions and because there are 800+ SKUs, I find it challenging to do so.
@taiyaki35 as a best practice, it will make sense to split product column in rows in pq and then you will able to achieve your result very easily.
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |