Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all
I need to extract the number and text in separated columns from a string. I tried out with power query's split column digit to non digit but didnt get the number as one:
From
to:
As seen on first row, i got the .9 in another column but should be as a one "18.9"
The expected result would be something like this:
Number | Text |
18.9 | Ltr |
2.5 | Ltr |
1.5 | Ltr |
3 | Ltr |
1.5 | Ltr |
1.25 | Ltr |
How can I get it right?
Pbix
https://1drv.ms/u/s!ApgeWwGTKtFdhnRraJ1oc7BvSq9D?e=sgbaLn
Thanks
Solved! Go to Solution.
Hi,
Right click and use Add column from example
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Not sure, try this (modified from the advanced editor in your .pbix)
let
Source = Excel.Workbook(File.Contents("C:\Users\O59393\Documents\Vol Capacity.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Split Column by Character Transition" = Table.SplitColumn(#"Promoted Headers", "[ L2.3 - Vol Capacity ]", Splitter.SplitTextByCharacterTransition({"0".."9","."}, (c) => not List.Contains({"0".."9","."}, c)), {"[ L2.3 - Vol Capacity ].1", "[ L2.3 - Vol Capacity ].2"})
in
#"Split Column by Character Transition"
If you right click on your column you can split it at the point where it changes from digit to non-digit.
We are not able to see your query in the query editor unless you also share the .xlsx file.
That's exactly how I did it but as seen in the image I posted the number after the dot was separated.
If you look on my pbix you will see I used your method.
Thanks!
Hi,
Right click and use Add column from example
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Ahh, the . is a switch to non-digit, gross. What about splitting by delimiter (space)?
Hmm weird, the table cannnot be displayed from the report tab for example? or the last step of the power query?
In power editor :
Select the column
right click
Click on Add column from examples
then write the number in the row here in the picture and click ok
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
It's can't, no but you can also modify the split line to include the "." in the part to keep.
= Table.SplitColumn(#"Changed Type", "Column", Splitter.SplitTextByCharacterTransition({"0".."9","."}, (c) => not List.Contains({"0".."9","."}, c)), {"Column.1", "Column.2"})
So it splits it when it goes form 0-9 OR . to something that is not 0-9 OR .
Im doing this on the M code but getting an error:
let
Source = Excel.Workbook(File.Contents("C:\Users\O59393\Documents\Vol Capacity.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
Table.SplitColumn(#"Changed Type", "Column", Splitter.SplitTextByCharacterTransition({"0".."9","."}, (c) => not List.Contains({"0".."9","."}, c)), {"Column.1", "Column.2"})
in
#"Filtered Rows"
What should I fix?
Not sure, try this (modified from the advanced editor in your .pbix)
let
Source = Excel.Workbook(File.Contents("C:\Users\O59393\Documents\Vol Capacity.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Split Column by Character Transition" = Table.SplitColumn(#"Promoted Headers", "[ L2.3 - Vol Capacity ]", Splitter.SplitTextByCharacterTransition({"0".."9","."}, (c) => not List.Contains({"0".."9","."}, c)), {"[ L2.3 - Vol Capacity ].1", "[ L2.3 - Vol Capacity ].2"})
in
#"Split Column by Character Transition"
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |