Split

[Anonymous]

Hi guys,

can anybody help me? Please

The column called phase_history has a sequence of steps in my pipeline and first_time_in has the sequence of dates referring to each step of phase_history. For example, in the figure below, the first step of the start form call circled in red, corresponds to the first date of the first_time_in column also circled in red. What could I do to get each step with the corresponding date?

The result I would like to get would be like the figure below.

phase_history	first_time_in
Start form	2021-07-14T00:27:17+00:00,
Primeiro contato ASAP	2021-07-14T00:27:18+00:00
Primeiro dia de contato	2021-07-14T00:36:48+00:00,

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5JLCpRSMsvytUJKMrMTc0syldIzs8rSSzJV3AMdgxAiKZkJiqkpMIklXSUjAyMDHUNzHUNTUIMDKyMzK0MzbWBDAMDHUwZC6wyxmZWJlAZpVgd6jjGIsTAELtj4DIYjgHLoDgmFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [phase_history = _t, first_time_in = _t]),

    ToRows = Table.ToRows(Source),
    #"Split Rows" = let cols = Table.ColumnNames(Source) in List.Transform(ToRows, each Table.FromColumns(List.Transform(_, each Text.Split(_, ",")), cols)),
    #"Combined Tables" = Table.Combine(#"Split Rows")
in
    #"Combined Tables"

[Anonymous]

@CNENFRNL I don't know how I could implement this in my code. I don't just have these 2 columns, I have others too, my code is something like this:

let
Source = Sql.Database(" "),
PipedeVendas = Source{[Schema=" ",Item="PipedeVendas"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(PipedeVendas,{"card_id", "title", "valor_do_deal", "ultimo_conteudo", "fonte", "phase_history", "first_time_in", "ultimo_termo", "ultima_campanha"}),
#"Extracted Text Between Delimiters" = Table.TransformColumns(#"Removed Other Columns", {{"phase_history", each Text.BetweenDelimiters(_, "[", "]"), type text}, {"first_time_in", each Text.BetweenDelimiters(_, "[", "]"), type text}})
in
#"Extracted Text Between Delimiters"

[mussaenda]

Hi @Anonymous ,

You can use split with delimiter.

If you need help on splitting, provide a workable data.

Thank you.

[Anonymous]

@mussaendaA sample of data follows. I've been trying and I can't get the result I need to get.

https://ufile.io/zmfwpgib

[mussaenda]

Hi @Anonymous ,

You attached pbix is connected to an excel where we cannot access.

Please provide also the excel connected to your sample pbix so we can help you to implement @CNENFRNL ' solution

 
Thank you

[v-rzhou-msft]

Hi @Anonymous 

I think  CNENFRNL 's reply has shown you a correct way to solve your problem. ToRow,Split Rows and Combined Tables.

It seems that I don't have access to your data source. I couldn't do transform in Power Query Editor. If you still have problem, you can build a sample by Enter Data instead of Get data.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

Hi @Anonymous 

One way is to use "Split columns by delimiter", please see the below links:

https://docs.microsoft.com/en-us/power-query/split-columns-delimiter

https://radacad.com/split-column-by-delimiter-in-power-bi-and-power-query

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Appreciate your Kudos ✌️!!