Forum Discussion
AdministratorProblem performing Text.Split to columns that are formatted (Data1; Data2; Data3; etc)
Hello!
First, thank you very much for taking the time to try to fix my problem, but I found another solution that suited my needs. This consists of the following steps:
#"EVT_INV_SPL agregada" = Table.AddColumn(#"null a NE EVT_ASI", "EVT_INV_SPL", each Text.Split([EVT_INV], "; ")), #"EVT_INS_SPL agregada" = Table.AddColumn(#"EVT_INV_SPL agregada", "EVT_INS_SPL", each Text.Split([EVT_INS], "; ")), #"EVT_ASI_SPL agregada" = Table.AddColumn(#"EVT_INS_SPL agregada", "EVT_ASI_SPL", each Text.Split([EVT_ASI], "; ")), #"Se expandió EVT_INV_SPL" = Table.ExpandListColumn(#"EVT_ASI_SPL agregada", "EVT_INV_SPL"), #"Filas filtradas" = Table.SelectRows(#"Se expandió EVT_INV_SPL", each ([EVT_INV_SPL] = "QV1" or [EVT_INV_SPL] = "QV2" or [EVT_INV_SPL] = "QV3" or [EVT_INV_SPL] = "QV4")), #"Inscrito (Si-No)" = Table.AddColumn(#"Filas filtradas", "Inscrito (Si-No)", each if List.Contains([EVT_INS_SPL], [EVT_INV_SPL]) then "Si" else "No"), #"Asistente (Si-No)" = Table.AddColumn(#"Inscrito (Si-No)", "Asistente (Si-No)", each if List.Contains([EVT_ASI_SPL], [EVT_INV_SPL]) then "Si" else "No")Which is based on using Text.Split() in the three columns (EVT_INV, EVT_INS and EVT_ASI) and then expanding only EVT_INV so I can have the contacts who were invited to the events with "QV" codes, then with List.Contains() I ask if a record in the EVT_INS list and EVT_ASI matches the EVT_INV code and if it matches then it is "Yes" , otherwise "No".
Again, thank you so much for taking the time to respond.
Best regards.
Hi Syndicate_Admin ,
Please try to apply the below codes in your Advanced Editor and check whether it can get what you want.
let
Source = Excel.Workbook(File.Contents("\*\Ejemplo columnas con SPLIT (EVT_INV, EVT_INS, EVT_ASI).xlsx"), null, true),
TablaContactos_Table = Source{[Item="TablaContactos",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(TablaContactos_Table,{{"Nombre Completo", type text}, {"Correo", type text}, {"Empresa", type text}, {"Tipo de Cargo (C)", type text}, {"País (C)", type text}, {"EVT_INV", type text}, {"EVT_INS", type text}, {"EVT_ASI", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Contain QV?", each if Text.Contains([EVT_INV], "QV") or Text.Contains([EVT_INS], "QV") or Text.Contains([EVT_ASI], "QV") then "Y" else "N"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each true)
in
#"Filtered Rows"
Best Regards
AdministratorHello!
First, thank you very much for taking the time to try to fix my problem, but I found another solution that suited my needs. This consists of the following steps:
#"EVT_INV_SPL agregada" = Table.AddColumn(#"null a NE EVT_ASI", "EVT_INV_SPL", each Text.Split([EVT_INV], "; ")),
#"EVT_INS_SPL agregada" = Table.AddColumn(#"EVT_INV_SPL agregada", "EVT_INS_SPL", each Text.Split([EVT_INS], "; ")),
#"EVT_ASI_SPL agregada" = Table.AddColumn(#"EVT_INS_SPL agregada", "EVT_ASI_SPL", each Text.Split([EVT_ASI], "; ")),
#"Se expandió EVT_INV_SPL" = Table.ExpandListColumn(#"EVT_ASI_SPL agregada", "EVT_INV_SPL"),
#"Filas filtradas" = Table.SelectRows(#"Se expandió EVT_INV_SPL", each ([EVT_INV_SPL] = "QV1" or [EVT_INV_SPL] = "QV2" or [EVT_INV_SPL] = "QV3" or [EVT_INV_SPL] = "QV4")),
#"Inscrito (Si-No)" = Table.AddColumn(#"Filas filtradas", "Inscrito (Si-No)", each if List.Contains([EVT_INS_SPL], [EVT_INV_SPL]) then "Si"
else "No"),
#"Asistente (Si-No)" = Table.AddColumn(#"Inscrito (Si-No)", "Asistente (Si-No)", each if List.Contains([EVT_ASI_SPL], [EVT_INV_SPL]) then "Si"
else "No")
Which is based on using Text.Split() in the three columns (EVT_INV, EVT_INS and EVT_ASI) and then expanding only EVT_INV so I can have the contacts who were invited to the events with "QV" codes, then with List.Contains() I ask if a record in the EVT_INS list and EVT_ASI matches the EVT_INV code and if it matches then it is "Yes" , otherwise "No".
Again, thank you so much for taking the time to respond.
Best regards.
Hi Syndicate_Admin ，
It's glad to hear that you got the solution for your problem. And thanks for sharing the solution here. Could you please mark your post as Answered? It will help the others in the community find the solution easily if they face the same problem with yours. Thank you.
Best Regards