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

 

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],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(TablaContactos_Table, {{"EVT_INV", Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "EVT_INV"),
    #"Added Index" = Table.AddIndexColumn(#"Split Column by Delimiter", "Index", 0, 1, Int64.Type)
in
    #"Added Index"

 

 

Go to Power Query, Get Data...Blank Query... Advanced Editor  and then paste this code in.  Adjust the path to the Excel file accordingly. I am counting 88150 rows, by the way. So your transforms must add filters.

You also seem to have some inconsistency in your sample data, for example people attended events they were not invited to etc.

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

    Indeed, I add the filters because I require only the information of the EVT columns that contain the QV code and mainly there the problem is generated, by generating the split in the EVT_INS column and EVT_ASI since EVT_INV begins to double 😞