Create a single matrix with different keyword search results

[taiyaki35]

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!

[v-yalanwu-msft · Accepted 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.

[taiyaki35]

Hi @parry2k , Got it. I will try figuring out the partterns and fix the data. Thank you! 

[parry2k]

@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.

[taiyaki35]

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. 

[parry2k]

@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.⚡