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

Is there a way to extract certain words out?

hidenseek9 — Wed, 13 Sep 2017 02:43:07 GMT

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,

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

MarcelBeug — Wed, 13 Sep 2017 06:10:47 GMT

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

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

hidenseek9 — Thu, 14 Sep 2017 01:21:01 GMT

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,

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

GabrielSantos — Thu, 14 Sep 2017 02:06:08 GMT

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

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

MarcelBeug — Thu, 14 Sep 2017 02:49:00 GMT

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.

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

Anonymous — Thu, 14 Sep 2017 06:05:42 GMT

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:

Comment:

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

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

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

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

hidenseek9 — Tue, 19 Sep 2017 06:04:36 GMT

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.

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

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.

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

and have word extract in a new column?

Many thanks,

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

MarcelBeug — Tue, 19 Sep 2017 06:41:33 GMT

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

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

hidenseek9 — Tue, 19 Sep 2017 06:58:35 GMT

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,

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

MarcelBeug — Tue, 19 Sep 2017 07:29:16 GMT

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.

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

hidenseek9 — Tue, 19 Sep 2017 08:05:49 GMT

MarcelBeug

Perfect!

Thank you so much for the help!