Forum Discussion
Split
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, |
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"
7 Replies
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"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"Hi Anonymous ,
You can use split with delimiter.
If you need help on splitting, provide a workable data.
Thank you.
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 ✌️!!
