cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hidenseek9 Member
Member

Is there a way to extract certain words out?

Hello Power BI Community!

 

I have a question regarding DAX expression.

I have a dummy data below.

In the file, I have a data with names of products and weight out of each product in text.

 

DummyData

 

What I would like to do is to extract just the text on weights (i.e. 75GX4PX6)

Then I would like to calculate the weight of the total product. (i.e. 75*4*6= 1,800)

 

However, the weight in the text is not listed in a consistant format or

a length of characters is not always the same.

 

Is there a way to extract just the weight text?

Or a direct way to calculate the total weight?

 

Many thanks,

 

H

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Is there a way to extract certain words out?

It depends.

 

If the pack size is always the third number, then you can adjust the "Product" step to have only the first 2 values multiplied.

 

    Product =               Table.TransformColumns(NumberFrom,{{"Weight", each List.Product(List.FirstN(_,2))}}),

 

Otherwise I'm curious how you are going to create the column with the number of packs per case.

Specializing in Power Query Formula Language (M)
10 REPLIES 10
Super User
Super User

Re: Is there a way to extract certain words out?

Probably Power Query would be the better tool for this.

 

I used only your one example in the code below, so you should adjust the source to yours if you want to use this solution.

 

Basically the solution first splits the text on any digits, then splits the original text again, using the result from the first split as new delimiters (resulting in the digit ranges). These are converted to numbers and multiplied with each other.

 

let
    Source =                #table(type table[Text on Weight = text],{{"75GX4PX6"}}),
    SplitTextOnDigits =     Table.AddColumn(Source, "Delimiters", each Text.SplitAny([Text on Weight],"0123456789")),
    RemoveBlanks1 =         Table.TransformColumns(SplitTextOnDigits,{{"Delimiters", each List.Select(_, each _ <> "")}}),
    SplitTextOnDelimiters = Table.AddColumn(RemoveBlanks1, "Weight", each Splitter.SplitTextByEachDelimiter([Delimiters])([Text on Weight])),
    RemoveBlanks2 =         Table.TransformColumns(SplitTextOnDelimiters,{{"Weight", each List.Select(_, each _ <> "")}}),
    NumberFrom =            Table.TransformColumns(RemoveBlanks2,{{"Weight", each List.Transform(_, Number.From)}}),
    Product =               Table.TransformColumns(NumberFrom,{{"Weight", List.Product}}),
    RemovedDelimiters =     Table.RemoveColumns(Product,{"Delimiters"})
in
    RemovedDelimiters
Specializing in Power Query Formula Language (M)
hidenseek9 Member
Member

Re: Is there a way to extract certain words out?

@MarcelBeug

 

Thank you for your response.

I am new to Power Query, so I am having a difficult time understanding

the formula.

 

I simply copy and pasted the formula you provided to me to

already existing query, but it comes out as an error.

I understand that reference names are wrong, but not sure how to fix it.

 

Would you be able to help me with this one?

 

Many thanks,

 

H

 

2017-09-14 10_16_34-Advanced Editor.png

 

2017-09-14 10_20_31-Weight data test - Power BI Desktop.png

GabrielSantos Regular Visitor
Regular Visitor

Re: Is there a way to extract certain words out?

If you can share the specific erroe message I might be able to help you.

Super User
Super User

Re: Is there a way to extract certain words out?

My column "Text on Weight" is your column "Data".

However, you forgot to change "Text on Weight" in step SplitTextOnDelimiters.

 

As @GabrielSantos already mentioned, it would be better to share the error message and the step in which the error mesage first ooccurs.

Specializing in Power Query Formula Language (M)
Community Support Team
Community Support Team

Re: Is there a way to extract certain words out?

Hi @hidenseek9,

 

You can try to use below formula to get the number list and calcualte the mutiple result.

 

Formula:

 

#"Added Custom" = Table.AddColumn(#"Previous Steps", "ColumnName", each List.Product(List.Transform(List.Select(Text.SplitAny([Data],Text.Combine({"A".."Z"," "})),each Number.Mod(try Number.From(_) otherwise null,1)=0), each Int32.From(_))))

 

Sample:

 

#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Values", each List.Product(List.Transform(List.Select(Text.SplitAny([Data],Text.Combine({"A".."Z"," "})),each Number.Mod(try Number.From(_) otherwise null,1)=0), each Int32.From(_))))

 

Result:

1.PNG

 

Comment:

Text.SplitAny([Data],Text.Combine({"A".."Z"," "})) //Use random alphabets as the separator to split original string to list.

3.PNG
List.Select(list,each Number.Mod(try Number.From(_) otherwise null,1)=0) //select each number items.

2.PNG
List.Transform(list, each Int32.From(_)) //transform text list to number list
List.Product(list) // get the product from a list of numbers.

 

 

Full query:

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\sample data.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Data"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Values", each List.Product(List.Transform(List.Select(Text.SplitAny([Data],Text.Combine({"A".."Z"," "})),each Number.Mod(try Number.From(_) otherwise null,1)=0), each Int32.From(_))))
in
    #"Added Custom"

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
hidenseek9 Member
Member

Re: Is there a way to extract certain words out?

@MarcelBeug

@GabrielSantos

 

Thank you for the input.

I applied the formula into Power BI and it does not work.

 

This is the formula I have under advanced editor query.

2017-09-19 14_47_07-Advanced Editor.png

 

Then the data appears like this. At this stage, the data looks okay.

2017-09-19 14_47_19-Weight data test - Query Editor.png

 

On the next step, the data appears like below and this is not working.

I would like to have word extract of weight, (i.e. 75G4PX6)

but the data is completely restructured.

2017-09-19 14_47_38-Re_ Is there a way to extract certain words out_ - Microsoft Power BI Community.png

 

Any way to keep the structure of the data the same,

and have word extract in a new column?

 

Many thanks,

 

H

 

 

 

Super User
Super User

Re: Is there a way to extract certain words out?

In step SplitTextOnDigits, you should use the name of the previous step, #"Renamed Columns", instead of Source.

 

Wrong reference.png

Specializing in Power Query Formula Language (M)
hidenseek9 Member
Member

Re: Is there a way to extract certain words out?

@MarcelBeug

 

Perfect! This works!

Thank you for the input.

 

One more question, though.

The problem is that the data has two different varieties.

1) 75G4PX6 (Meaning that 1 Pack = 75G *4, 1 case = 6 packs)

2) 110G1P (Meaning that 1 pack = 110G*1)

 

Some text has weight by a case, and others are based on a pack.

What I would like now to do is to have a column that will have the weight per pack.

Then I will add a column with a number of packs per case,

and simply multiply two columns to get the total weight by case.

 

How can I change the query editor, so that it will just calculate the weight per pack?

 

Many thanks,

 

H

Super User
Super User

Re: Is there a way to extract certain words out?

It depends.

 

If the pack size is always the third number, then you can adjust the "Product" step to have only the first 2 values multiplied.

 

    Product =               Table.TransformColumns(NumberFrom,{{"Weight", each List.Product(List.FirstN(_,2))}}),

 

Otherwise I'm curious how you are going to create the column with the number of packs per case.

Specializing in Power Query Formula Language (M)