topic Re: Need Help in Data Formating in Power Query

Need Help in Data Formating

Anonymous — Sat, 18 Sep 2021 17:22:46 GMT

Hello,

I have a column that contains special character as well as middle space and number, i am looking to extract only number without getting any middle space in it.

Please help, i tried all method to get the same.

Suresh

Re: Need Help in Data Formating

Greg_Deckler — Sat, 18 Sep 2021 17:32:42 GMT

Anonymous Sample data?

Re: Need Help in Data Formating

Anonymous — Sat, 18 Sep 2021 17:35:47 GMT

Sir, its like (0012345 78A. In this we have special character and albhabet as well as spacin between.

Re: Need Help in Data Formating

Greg_Deckler — Sat, 18 Sep 2021 18:15:22 GMT

Anonymous Well, in that particular case you could do:

Text.Replace(Text.Middle([Column1],1,10)," ","")

Re: Need Help in Data Formating

Anonymous — Sun, 19 Sep 2021 16:14:24 GMT

Hi Sir,

My reference column is "Ref ID" from where i am trying you extract only number, however not able to go through form both solution i tried - "Solution 1" and "Solution 2".

Below which i tried...

=Text.Replace(Text.Middle([Ref ID],1,10)," ","")),

= Text.Select([Ref ID],{"0".."9"}))

Please help.

Thanks

Re: Need Help in Data Formating

Jakinta — Tue, 21 Sep 2021 03:58:26 GMT

If you change [Ref ID] column type to text, it should work.

= Text.Select( Text.From ( [ #"Ref ID" ]) , {"0".."9"} )

Re: Need Help in Data Formating

wdx223_Daniel — Mon, 20 Sep 2021 07:24:36 GMT

=Text.Remove(Text.From([Ref ID]??"")," ")

Re: Need Help in Data Formating

v-kelly-msft — Tue, 21 Sep 2021 02:27:40 GMT

Hi Anonymous ,

Because your data contains 2 types of data which causes such error,using below expression instead:

= Table.TransformColumnTypes(Table.AddColumn(#"Appended Query", "Custom", each Text.Select(Text.From([Column1]),{"0".."9"})),{{"Custom", Int64.Type}})

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my raeply as a solution!