Log In
Register
Home
Active Member
December 29, 2022
Offline
Hi everyone. I would really appreciate some assistance.
For the past few days I have meet failing to extract machine names from specified column in Power Query. I added a new custom column and used the formula below. The objective is to extract the machine names [Machine 1], [Machine 2], [Machine 3], [Slitter], [Tissue Roller] and [Tissue Cutter] from the columns [Production_Units], [Roller_Weight_Type], [Roller_Serial_Numbers], [Machine_Operators] and [Machine_Hours]. Only machine names from [Production_Units] and [Roller_Weight_Type] are correctly extracted. The others are generateing errors. I have also attached the data table. ChatGPt has struggled to provide a solution. I’m sure we can teach AI a thing or two
= if Text.Contains([Production_Units], "Machine 1") then "Machine 1"
else if Text.Contains([Production_Units], "Machine 2") then "Machine 2"
else if Text.Contains([Production_Units], "Machine 3") then "Machine 3"
else if Text.Contains([Production_Units], "Slitter") then "Slitter"
else if Text.Contains([Production_Units], "Tissue Roller") then "Tissue Roller"
else if Text.Contains([Production_Units], "Tissue Cutter") then "Tissue Cutter"
else if Text.Contains([Roller_Weight_Type], "Slitter") then "Slitter"
else if Text.Contains([Roller_Weight_Type], "Machine 1") then "Machine 1"
else if Text.Contains([Roller_Weight_Type], "Machine 2") then "Machine 2"
else if Text.Contains([Roller_Weight_Type], "Machine 3") then "Machine 3"
else if Text.Contains([Roller_Weight_Type], "Tissue Roller") then "Tissue Roller"
else if Text.Contains([Roller_Weight_Type], "Tissue Cutter") then "Tissue Cutter"
else if Text.Contains([Roller_Serial_Numbers], "Slitter") then "Slitter"
else if Text.Contains([Roller_Serial_Numbers], "Machine 1") then "Machine 1"
else if Text.Contains([Roller_Serial_Numbers], "Machine 2") then "Machine 2"
else if Text.Contains([Roller_Serial_Numbers], "Machine 3") then "Machine 3"
else if Text.Contains([Roller_Serial_Numbers], "Tissue Roller") then "Tissue Roller"
else if Text.Contains([Roller_Serial_Numbers], "Tissue Cutter") then "Tissue Cutter"
else if Text.Contains([Machine_Operators], "Slitter") then "Slitter"
else if Text.Contains([Machine_Operators], "Machine 1") then "Machine 1"
else if Text.Contains([Machine_Operators], "Machine 2") then "Machine 2"
else if Text.Contains([Machine_Operators], "Machine 3") then "Machine 3"
else if Text.Contains([Machine_Operators], "Tissue Roller") then "Tissue Roller"
else if Text.Contains([Machine_Operators], "Tissue Cutter") then "Tissue Cutter"
else if Text.Contains([Machine_Hours], "Slitter") then "Slitter"
else if Text.Contains([Machine_Hours], "Machine 1") then "Machine 1"
else if Text.Contains([Machine_Hours], "Machine 2") then "Machine 2"
else if Text.Contains([Machine_Hours], "Machine 3") then "Machine 3"
else if Text.Contains([Machine_Hours], "Tissue Roller") then "Tissue Roller"
else if Text.Contains([Machine_Hours], "Tissue Cutter") then "Tissue Cutter"
else null
Moderators
January 31, 2022
Offline
Hi,
The query in your file connects to a Table1 that is not part of the file. That makes it difficult to follow through what you are trying to achieve.
Having said that, ChatGPT didn't do a very good job, I'm afraid. Most likely you should have started unpivoting the table first and then do some "replace values" (perhaps) and then extract the machine codes. Not sure though. Can you please clarify?
1 Guest(s)
