Forum Discussion
HELP PLEASE. Vlookup Hybrid
Hello, I would appreciate some guidance. I don't know if i will use the right terminology, hopefully the pictures will help.
I have really filthy DATA!
I want to get the answers of the title into a unique coloumn.
I have tried "Text to column" with seperating ~, i realise that not all the ProjectID will be in the same column as one row may only have 3 input and another 4... hence it wont be in the same column.
In normal excel Micro could be run to look for the key word and then the answer to be taken out into a unique column.
How do i do that in Power BI/ Power Query?
I want to look up Column N, Column O, Column P for <HX Accountability> With answer 1 character after <HX Accountability> into Column
Thank you in advance!
Hi Anonymous
Please provide some raw sample data. I can't tell from your image what columns you mean by Columns N, O and P. In Power Query the code needs to refer to the column name, not the lketter used in Excel.
In your image I can see 2 occurrences of HX Accountability - I presume you want to extract text after the second occurrence?
Please provide examples of what you weant as a result, it makes it easier to give you a solution.
Regards
Phil
Hi Anonymous
Try this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xVRNbxpBDP0r1p4WCcSWCg572wZQD4DU5NIqy8GZ9aYuwwyaj6SoSn57vUNSpVUSEdSqF0v2PNvPft69vMw+VTCAj5+hUspGE/CKNYd9CT+ef6jrlb27P2e/gdkNmeAFuRpWkCvNagPonL2FYIG+79A04EmTCiCIXl2Px0UJyYh/l/WzfI4qeKhjUYwmMGfTsLn2UELvPv+AanPtpHMDuY1BsyGwLbBR3Ejf3gG1nE07mlPyyvEVwRLZBDJoFMFMuLjnYYn64akyqPeePeTfog/cssLA1kDHnm66XopeaBa+0sOA1ICyJkg0BhkBWpnrsfc5KbvdkmkOdXNHW2oYNQhGAg+1z6xROvoOIX627v9nZX7n86Y1HTfwX5f+iaYnXsUxep6mzBfyf0qzuIBRMSrq+n0ha09mcTEoxiXMkXV01GmFu53ew3K2EDrOCTHxZOND4dKRrabTBHkUQxN2a0yZ7JTDNsjWJAOi8djS0FgzEHEkA2VXPmCg/i8ik9GkhGSESBcaFO9g9SRDJRlurduAQtdIaR9cTKKmmznl0t90Wf/0n3Dc1/zK2b54kNl6/RM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "HX Accountable", each if Text.Contains([Column1], "HX Accountability\Yes") then "Yes" else "No"), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Risk Event", each if [HX Accountable] = "Yes" then Text.BeforeDelimiter(Text.AfterDelimiter([Column1], "Risk Events: {"), ":") else "N/A") in #"Added Custom1"The : has been removed from the end of the Risk Event code, but if you need it, it's easy to put back.
Regards
Phil
4 Replies
Hi Anonymous
Try this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xVRNbxpBDP0r1p4WCcSWCg572wZQD4DU5NIqy8GZ9aYuwwyaj6SoSn57vUNSpVUSEdSqF0v2PNvPft69vMw+VTCAj5+hUspGE/CKNYd9CT+ef6jrlb27P2e/gdkNmeAFuRpWkCvNagPonL2FYIG+79A04EmTCiCIXl2Px0UJyYh/l/WzfI4qeKhjUYwmMGfTsLn2UELvPv+AanPtpHMDuY1BsyGwLbBR3Ejf3gG1nE07mlPyyvEVwRLZBDJoFMFMuLjnYYn64akyqPeePeTfog/cssLA1kDHnm66XopeaBa+0sOA1ICyJkg0BhkBWpnrsfc5KbvdkmkOdXNHW2oYNQhGAg+1z6xROvoOIX627v9nZX7n86Y1HTfwX5f+iaYnXsUxep6mzBfyf0qzuIBRMSrq+n0ha09mcTEoxiXMkXV01GmFu53ew3K2EDrOCTHxZOND4dKRrabTBHkUQxN2a0yZ7JTDNsjWJAOi8djS0FgzEHEkA2VXPmCg/i8ik9GkhGSESBcaFO9g9SRDJRlurduAQtdIaR9cTKKmmznl0t90Wf/0n3Dc1/zK2b54kNl6/RM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "HX Accountable", each if Text.Contains([Column1], "HX Accountability\Yes") then "Yes" else "No"), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Risk Event", each if [HX Accountable] = "Yes" then Text.BeforeDelimiter(Text.AfterDelimiter([Column1], "Risk Events: {"), ":") else "N/A") in #"Added Custom1"The : has been removed from the end of the Risk Event code, but if you need it, it's easy to put back.
Regards
Phil
Hi Anonymous
Please provide some raw sample data. I can't tell from your image what columns you mean by Columns N, O and P. In Power Query the code needs to refer to the column name, not the lketter used in Excel.
In your image I can see 2 occurrences of HX Accountability - I presume you want to extract text after the second occurrence?
Please provide examples of what you weant as a result, it makes it easier to give you a solution.
Regards
Phil
QA - HX Accountability: {QA - HX Accountability\No}~Risk Events: {N/A (click arrow to expand select N/A)\550: 550: N/A} (Facts & Findings : )~(Background (outline of incident) : )~(MEDA - Describe Maintenance Error : )~(MEDA - Describe Event : )~(Analysis (justification and evidence) : )~(MEDA - Describe the selected contributing factor : )~(Recommendation (remedial action) : )~(Conclusion : ) QA - HX Accountability: {QA - HX Accountability\No}~Risk Events: {N/A (click arrow to expand select N/A)\550: 550: N/A} (Conclusion : )~(Analysis (justification and evidence) : )~(Recommendation (remedial action) : )~(Facts & Findings : )~(Background (outline of incident) : )~(MEDA - Describe Event : )~(MEDA - Describe Maintenance Error : )~(MEDA - Describe the selected contributing factor : ) QA - HX Accountability: {QA - HX Accountability\Yes}~Risk Events: {LS 2020\300: 300: LS-05: Failure to apply MEL correctly and/or the ADD application leading to aircraft in an unsafe/non-compliant state,LS 2020\626: 626: LS-2020-01 Non-compliance of work card instruction} (Recommendation (remedial action) : )~(Conclusion : )~(Analysis (justification and evidence) : )~(Background (outline of incident) : )~(MEDA - Describe Maintenance Error : )~(MEDA - Describe the selected contributing factor : )~(Facts & Findings : )~(MEDA - Describe Event : )