cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kfreitass
Frequent Visitor

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?

 

kfreitass_1-1630736022851.png

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

phase_historyfirst_time_in
Start form2021-07-14T00:27:17+00:00,
Primeiro contato ASAP2021-07-14T00:27:18+00:00
Primeiro dia de contato2021-07-14T00:36:48+00:00,

 

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

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"

Screenshot 2021-09-04 214335.png

View solution in original post

7 REPLIES 7
CNENFRNL
Community Champion
Community Champion

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"

Screenshot 2021-09-04 214335.png

View solution in original post

@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"

 

kfreitass_0-1630792492820.png

 

Hi @kfreitass ,

 

You can use split with delimiter.

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

 

Thank you.

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

 

https://ufile.io/zmfwpgib

 

Hi @kfreitass ,

 

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

Hi @kfreitass 

I think   '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
Resident Rockstar
Resident Rockstar

Hi @kfreitass 

 

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 ✌️!!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!