number from first 10 characters in a string

[MartinL]

Hi

can someone help me write a DAX formula to get the first set of numbers from within a test string
for example

123 _ a long red boat with 2 oars = 123
ABCD123 a long red boat with 2 oars = 123
ABCD 123 a long red boat with 2 oars = 123
abcd (123) a long red boat with 2 oars = 123

I'm importing an updateable excel sheet so I cant use a UDF in my original sheet
but if I import it to a template workbook in powerpivot can I have a piece of code that would work it out for me either in the table frame or within a pivot table?

Martin

 

[Matt Allington]

It can probably be done, but my advice is that there is probably a better way. I would be considering using Power Query to clean your data before loading to Power Pivot. Power Pivot is a reporting tool - it expects clean data. Power Query is a data transformation tool - it loves dirty data. My advice is use Power Query to deliver cleansed data to Power Pivot, and then write Power Pivot formulas to work on clean data.

Just my opinion.

 

[MartinL]

I would be considering using Power Query to clean your data before loading to Power Pivot.

Thanks for the heads up Matt but you have left me in limbo.

Can you point me in the right direction of your suggestion.

Martin

 

[ImkeF]

Unfortunately I cannot think of an easy way for a beginner using Power Query's user-interface only, as we're missing clear patterns here on which we could simply split the data. So you have to use some (already a bit advanced) code here (just one of possible other solutions of course):

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    SplitTextIntoSeparateCharacters = Table.AddColumn(Source, "Custom", each Text.ToList(Text.Range([Spalte1],0,9))),
    ShowTheSplit = Table.ExpandListColumn(SplitTextIntoSeparateCharacters, "Custom"),
    CreateTableWithNumbers = Table.FromValue({"0", "1", "2", "3","4","5","6","7","8","9"}),
    FilterUsingInnerJoin = Table.NestedJoin(ShowTheSplit,{"Custom"},CreateTableWithNumbers,{"Value"},"NewColumn",JoinKind.Inner),
    RearrangeNumbers = Table.Group(FilterUsingInnerJoin, {"Spalte1"}, {{"Count", each Text.Combine(_[Custom], "")}})
in
    RearrangeNumbers

LinkToFile

 

[MartinL]

Imke

Whoa that's so new......

Thanks for the file.

For the record I've installed power query and have a copy of M is for Data Monkey for my bed time reading.
Hopefully I can start to work how this is working.
I have a good level of experience in Excel, VBA and SQL so I'm hoping its not too much of an uphill battle.

Thanks for your assistance.

Martin

 

[MartinL]

Just working through this...
On this line
Text.ToList(Text.Range([Spalte1],0,9))

If the string is less than 9 the whole thing fails.
I guess this could be amended to be if(len([Spalte1])>=10,9,len([Spalte1]) or something like it

if the length of the string in the active cell is less than 10 characters then calculate the length of the string and use that.

Sorry guys its a learning curve...

[Spalte1 = Column1]

Martin

 

[MartinL]

Holy cow I thinks I've done it

if Text.Length([Column1]) >9 
then Text.ToList(Text.Range([Column1],0,9))
else Text.ToList(Text.Range([Column1],0,Text.Length([Column1]) ))

It took ages to realise if then else needs to be in lower case
and len([Column1]) is Text.Length([Column1]) in proper case

Thanks for the help guys

Martin

 

[ImkeF]

Congrats !

If you have some other languages in the background, have a look at these resources as well - might help you move on fast here:

https://msdn.microsoft.com/en-us/library/mt270235.aspx

http://pqreference.azurewebsites.net/PowerQueryFormulaLanguageSpecificationAugust2015.pdf
https://msdn.microsoft.com/en-us/library/mt211003.aspx

 

[FDibbins]

This formula would extract the 1st set of contiguous numbers for you, maybe into a helper column?
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))) Courtesy of:*Ron Coderre