cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
taiyaki35
Microsoft
Microsoft

Create a single matrix with different keyword search results

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 NameValue
A-B21
A-B-C37
B-C45
C-D56
D18

 

CategoryValue
A48
B103
C138
D74

 

Thank you for the support!

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1624863319792.png

Method 2: split and unpivot in power query

a)split column

vyalanwumsft_1-1624863403591.png

b)unpivot columns

vyalanwumsft_2-1624863454305.png

c)group by column

vyalanwumsft_3-1624863505125.png

The final output is shown below:

vyalanwumsft_4-1624863540371.png

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.

 

 

View solution in original post

5 REPLIES 5
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1624863319792.png

Method 2: split and unpivot in power query

a)split column

vyalanwumsft_1-1624863403591.png

b)unpivot columns

vyalanwumsft_2-1624863454305.png

c)group by column

vyalanwumsft_3-1624863505125.png

The final output is shown below:

vyalanwumsft_4-1624863540371.png

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.

 

 

View solution in original post

taiyaki35
Microsoft
Microsoft

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

parry2k
Super User
Super User

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






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.





taiyaki35
Microsoft
Microsoft

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
Super User
Super User

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






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.





Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!