SQL : how do I populate a custom table/column based on the value in a different column
Sorry in advance if this is a dumb question. I am not very skilled in SQL/Power BI.
I have a table that I am working with which has a column Job Names.
I want to populate either a custom table/column (I think a separate table might be best for my needs but any progress in the right direction is extremely welcomed) based on a substring found within this column.
The column will always contain 1 of 4 potential numbers but the number can be located in various spots within the name and there is a chance that another number found in the job name might be representing one of the numbers unintentionally.
Here is an example:
I have highlighted in red the numeric string which is important and added a few example of how it could be duplicated unintentionally in the job name and the associated desired output value. I feel like maybe a case statement here would be the best route but as I mentioned before, I am not very skilled in this area.
The reason for this is I have a datastream I am trying to use in Power BI, but I want to create a page filter based on these custom values and since I have no column which contains just these specific numbers I am unable to create a slicer visualization to filter the data shown on the report.
If any clarification is needed just let me know I am happy to try and rephrase anything or be more specific where needed :)
In the power query window, select the job name column, and then add add a letter on the front and back of the text. Transform Tab > Text Column Group > Format > Add Prefix/Add Suffix Then use split column (also in the Text Column Group) to split Digit to Non-Digit and then Non-Digit to Digit. Then delete the extra columns and there you are! Here is the M code:
#"Added Prefix" = Table.TransformColumns(#"Changed Type", {{"JOB_NAME", each "x" & _, type text}}),
#"Added Suffix" = Table.TransformColumns(#"Added Prefix", {{"JOB_NAME", each _ & "x", type text}}),
#"Split Column by Character Transition" = Table.SplitColumn(#"Added Suffix", "JOB_NAME", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"JOB_NAME.1", "JOB_NAME.2"}),
#"Split Column by Character Transition1" = Table.SplitColumn(#"Split Column by Character Transition", "JOB_NAME.2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"JOB_NAME", "JOB_NAME.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition1",{"JOB_NAME.1", "JOB_NAME.2"})
- How to properly delete data from a table and chain the deletion
- Find procedure name in all other procedures
- SQL-How to Insert Row Without Auto incrementing a ID Column?
- Why can't I use an alias in a DELETE statement?
- How do I perform a GROUP BY on an aliased column in SQL Server?
- Running multiple SQL-Statements with Python
- Using rangeBetween considering months rather than days in PySpark
- How to find databases which accessible to me in Sql server?
- data return incorrect using left join with 2 tables
- Repeat rows specified number of times in PostgreSQL
- How to visualize database tables in postgresql using pgAdmin?
- Odata override behaviour of orderby
- Unable to open BCP host data-file
- How to join parent object with nested array using OpenJson SQL
- batch update a column using sql
- Queries using LIKE wildcards in sql server
- Conditional aggregation in SQL: display condition-dependent count in percentage?
- Parsing JSON with Oracle SQL without knowing incoming field names
- In a standard MS Access SQL query output that does not have any aliases, how do I replace the full names by their "first-letters" aliases?
- Convert SQL containing GROUP BY and SUM() to active record in CodeIgniter
- output sql data to html with hyperlink
- Update all column names to be lowercase
- SQL Server pivoting on data without an aggregate
- Flagging a row in new column based on conditions on previous and current rows
- replace or remove characters and then cast to decimal
- Export specific rows from a PostgreSQL table as INSERT SQL script
- Query to get only numbers from a string
- SQL grouping result to half hour
- table is specified twice both as a target for INSERT and as separate source of data
- SQL to JSON Need Bracket for Array that can have multiple rows