Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
5 years ago
Solved

Problem performing Text.Split to columns that are formatted (Data1; Data2; Data3; etc)

Good morning dear, It is my first post in the Power BI forum since I am starting with this, I hope to receive your help to strengthen my learning and knowledge, very grateful in advance. The doub...
  • Syndicate_Admin's avatar
    Syndicate_Admin
    5 years ago

    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.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

Yes, but that should not be a surprise to you. Effectively you are multiplying all split values in EVT_INV by all split values in EVT_INS by all split values in EVT_ASI - unless you can provide a business logic that only picks individual combinations like the QV filter you mentioned.  Should that be applied to all three splits?

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    5 years ago

    That's the problem I have, I'm starting with Power Query and Power BI and I have no knowledge of whether it is possible to split the 3 columns and leave only the information that is related to the "QV" filter, without the problem of repeating the values of EVT_INV. Create a column with a result If - No, that will query whether EVT_INS or EVT_ASI had the same code as EVT_INV but filtering by If it leaves me only the matches as expected and removes the other codes associated with Invitations that do not have Enrollments or that have not been attended.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

      • Syndicate_Admin's avatar
        Syndicate_Admin
        Icon for Administrator rankAdministrator
        5 years ago

        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.