Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I'm fairly new to Power BI and am hoping some more experienced users could help point me in the right direction.
I have a data set of transactions from a procurement system that includes a column originating from a 'free-form' entry field into which users can manually enter a purchase order number. Not all records have a purchase order number and the data can include both numeric purchase order details as well as random text information.
I'm trying to modify my query to extract from any records that contain a purchase order number, the actual purchase order number and return it to a new column in the query. If I was using Excel I would use a CONTAIN filter but as I don't want to filter I need some way to extract the 11 digit number and return it to a separate column which retaining all other records that don't have a purchase order number in this column.
For example the numbering sequence for all purchase orders begins with either '4500' or '4700' and are 10 characters long, so I'd like to a) recognise each record that contains '4500' or '4700' and extract the first 10 characters. Any records that don't match this criteria would simply remain blank.
An example of the data set and desired outcome is tabled below.
Existing data field | Desired result |
No purchase order | 0 or "Blank" |
No purchase order | 0 or "Blank" |
No purchase order | 0 or "Blank" |
No purchase order | 0 or "Blank" |
45000123456 Vendor purchase order details | 45000123456 |
45000123457 Vendor purchase order details | 45000123457 |
45000123458 Vendor purchase order details | 45000123458 |
45000123459 Vendor purchase order details | 45000123459 |
47000112233 Vendor purchase order details | 47000112233 |
47000112234 Vendor purchase order details | 47000112234 |
47000112235 Vendor purchase order details | 47000112235 |
Many thanks.
Solved! Go to Solution.
So in your query under "Add Column" you could click the "Add Custom Column" button and then paste in logic like the following:
if Text.StartsWith([Existing data field],"4500")
or Text.StartsWith([Existing data field],"4700")
then Text.Start([Existing data field],10)
else null
So in your query under "Add Column" you could click the "Add Custom Column" button and then paste in logic like the following:
if Text.StartsWith([Existing data field],"4500")
or Text.StartsWith([Existing data field],"4700")
then Text.Start([Existing data field],10)
else null
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |