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 have a table in power bi called Post_Inspections. This table has a free text column call Post Inspect notes that cantains comments and codes. See example below.
Post_Inspections table
Post Inspect Notes |
Spoke to tenant who conffirmedd that the isssue had been resolved to her satisfaction. Resolved DMRSV. Cause CAAMC |
Spoke to tenant all plastering works completed to a satisfactory standard. No further issues resolved = DMRV Other = CAOTH |
Spoke to tenant who confirmed that the works have been carried out andd there are no further issuess. Resolved = DMRSV cause = CAAMC |
Spoke to tenant confirmed there were no further issues. Resolved = DMRSV Cause = CAAMC |
Spoke to tenant who confirmed that all works have now been completed and mould issues have now been resolved. DMRSV CAAMC |
Spoke to customer who confirmed all works completed and the issues have been resolved =DMRV. Cause was CAAMC |
Spoke to tenant who confirmed that the mould issues had been resolved. DMRSV CAAMC |
Spoke to tenant today who conirme that issue with mould had been resolved satisfactorily DMRSV. Cause CAAMC |
Spoke to tenant who confirmed issue had been resolved satisfactorily. Resolved DMRSV CAAMC |
Spoke to tenant who confirmed all issues had been resolved and the mould has not returned. DMRSV CAAMC |
Spoke to tenant today who confirmed that the mould issues have been resolved appropriately. DMRSV CAAMC |
Spoke to tenant who confirmed that the works had been completed and that the mould had not returned. DMRSV CAAMC |
Spoke to tenant who confirmed that the issue with mould had now been resolved. DMRSV CAAMC |
Spoke to tenant who confirmed that the works have been carried out and the mould issues have been resolved. DMRSV CAAMC |
I also have a table called codes table which contains a list of codes thatb I wish to extract
Codes Table
Code |
CAAMC |
CAOTH |
CASER |
CAURD |
CAURL |
CAWAL |
DMESC |
DMFUR |
DMFWR |
DMNEWDM |
DMRSV |
Is it possible to use ower query to extract the codes from the Post Inspections table , if they appear on the seperate Codes Table and display these codes seperately in a new column in the Post Inspections table
thank you
Richard
Solved! Go to Solution.
Yeah, it's possible. You could add this step:
=
let
codeList = Codes[Code]
in
Table.AddColumn(
PreviousStep,
"Codes In Notes",
each
Text.Combine(
List.Select(
codeList,
(c) => Text.Contains([Post Inspect Notes], c)
),
","
),
type text
)
where PreviousStep is your previous step's name in Power Query.
(Assuming 'Codes' Table is actually named Codes and its column Code)
How that works is it reads the Code list once, and then filters it for each row for whatever code is found inside the notes. Last, it combines it to text in order to keep the result in one row.
Now, if you then need to filter, then you could leave the list as is, take the whole thing to a new table, expand it to new rows and then relate both. Or you could add a true/false column for each code automatically. It's all dependent on what you'd like to do with that result.
The result should look like this:
Cheers,
Thank you Smauro. I tried again this morning and your code works fine 😀
Thank you for your quick response. I am still quite a novice with Mcode. So I am struggling to add your line of code to a new step. Below are my transformations that are against the posy inspections table. Are you able to show me how to succesfullty as your peice of code thank you RIchard
let
Source = Sql.Database("blahblahblah"),
Repairs_Job_PostInspections = Source{[Schema="Repairs",Item="Job_PostInspections"]}[Data],
#"Renamed Columns" = Table.RenameColumns(Repairs_Job_PostInspections,{{"Inspector", "Post Inspector#"}}),
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Repairs_Job_Id", "Post Inspector#", "Inspection_Date", "Inspection_Notes", "Inspection_Satisfactory YN", "Inspection_Status"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Inspection_Date] > #date(2022, 4, 1)),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Repairs_Job_Id"}, #"Inspections job# only", {"Job#"}, "Inspections job# only", JoinKind.LeftOuter),
#"Expanded Inspections job# only" = Table.ExpandTableColumn(#"Merged Queries", "Inspections job# only", {"Supplier Name"}, {"Supplier Name"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Inspections job# only", each ([Supplier Name] = "Asset Management - Inspections")),
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows1"),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Duplicates",{{"Inspection_Date", "Post Inspect_Date"}, {"Inspection_Satisfactory YN", "Post Inspect Satisfactory"}, {"Inspection_Notes", "Post Inspect Notes"}}),
#"Merged Queries1" = Table.NestedJoin(#"Renamed Columns1", {"Post Inspector#"}, Operatives, {"Operative"}, "Operatives", JoinKind.LeftOuter),
#"Expanded Operatives" = Table.ExpandTableColumn(#"Merged Queries1", "Operatives", {"Operative Name"}, {"Operative Name"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded Operatives",{{"Operative Name", "Post Inspected By"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns2", "Post Inspect Notes", "Post Inspect Notes - Copy"),
#"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Repairs_Job_Id", "Post Inspector#", "Post Inspect_Date", "Post Inspect Notes", "Post Inspect Satisfactory", "Inspection_Status", "Supplier Name", "Post Inspect Notes - Copy", "Post Inspected By"})
in
#"Reordered Columns"
Yeah, it's possible. You could add this step:
=
let
codeList = Codes[Code]
in
Table.AddColumn(
PreviousStep,
"Codes In Notes",
each
Text.Combine(
List.Select(
codeList,
(c) => Text.Contains([Post Inspect Notes], c)
),
","
),
type text
)
where PreviousStep is your previous step's name in Power Query.
(Assuming 'Codes' Table is actually named Codes and its column Code)
How that works is it reads the Code list once, and then filters it for each row for whatever code is found inside the notes. Last, it combines it to text in order to keep the result in one row.
Now, if you then need to filter, then you could leave the list as is, take the whole thing to a new table, expand it to new rows and then relate both. Or you could add a true/false column for each code automatically. It's all dependent on what you'd like to do with that result.
The result should look like this:
Cheers,
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.