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

To avoid this kind of data loss/aggregation you may want to consider to add an index column to your data (at the beginning of your transform steps) , to make sure each row is unique.

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

    Thank you very much for the answer, could you be so kind as to guide me a little bit with the idea?

    I gave myself the job of creating an imitation of the DataSet that I am using, the important columns such as EVT_INV, EVT_INS and EVT_ASI are the same as the original and there are some steps taken in which I was comparing the results after performing the split to the other columns. Here is the link to the file.

    Thank you very much in advance and apologies for the inconvenience.