Extracting and Flattening an embedded table within a data set

[CaptOdyssey]

Is there an elegant method using Power Query of extracting an embedded table from multiple columns? In my example below Columns 3 (RateDifferentialPercentages) & 4 (RateDiffEffectiveDTs) represent an embedded table with comma separated values within Contract_No ABC000001. There is a -3% rate differential effective 01/12/2018 10:30:00 AM (local time). There is also a 0% rate differential effective 01/01/2019 10:30:00 AM (local time). Ideally, the solution would generate two rows of data for Contract_No ABC000001. In some cases there is only a single rate differential reported. In other cases there are multiple historical rate differentials reported.

Contract_No	Title	RateDifferentialPercentages	RateDiffEffectiveDTs
ABC000001	CustomerContract1	-3,0	01/12/2018 10:30:00 AM,01/01/2019 10:30:00 AM
ABC000002	CustomerContract2	2,0	01/01/2023 10:30:00 AM,01/01/2025 10:30:00 AM
ABC000003	CustomerContract3	-10,0	01/01/2022 10:30:00 AM,01/01/2024 10:30:00 AM
ABC000004	CustomerContract4	-3,0	01/12/2018 10:30:00 AM,01/01/2019 10:30:00 AM
ABC000005	CustomerContract5	-15,0	09/17/2007 10:30:00 AM,01/01/2011 11:30:00 AM
ABC000006	CustomerContract6	-3,0	01/12/2018 10:30:00 AM,01/01/2019 10:30:00 AM
ABC000007	CustomerContract7	-3,0	01/12/2018 10:30:00 AM,01/01/2019 10:30:00 AM
ABC000008	CustomerContract8	2,0	01/01/2023 10:30:00 AM,01/01/2025 10:30:00 AM
ABC000009	CustomerContract9	-15,0	05/05/2008 10:30:00 AM,01/01/2011 11:30:00 AM
ABC000010	CustomerContract10	-5,-3.5,-2.5,-1.5,0	12/31/2002 11:30:00 AM,01/01/2007 11:30:00 AM,01/01/2008 11:30:00 AM,01/01/2009 11:30:00 AM,01/01/2010 11:30:00 AM
ABC000011	CustomerContract11	3,5,6,7	09/16/2013 09:30:00 AM,01/01/2019 10:30:00 AM,01/01/2023 10:30:00 AM,01/01/2025 10:30:00 AM
ABC000012	CustomerContract12	10,0	01/01/2003 10:30:00 AM,04/08/2005 09:30:00 AM
ABC000013	CustomerContract13	1.5,3,5,0	01/01/2008 10:30:00 AM,01/01/2009 10:30:00 AM,01/01/2019 10:30:00 AM,06/17/2022 09:30:00 AM
ABC000014	CustomerContract14	1.5,3,5,6,7	01/01/2008 10:30:00 AM,01/01/2009 10:30:00 AM,01/01/2019 10:30:00 AM,01/01/2023 10:30:00 AM,01/01/2025 10:30:00 AM
ABC000015	CustomerContract15	-1,0	02/15/2019 10:30:00 AM,01/01/2020 10:30:00 AM
ABC000016	CustomerContract16	3,5,6,7	01/01/2010 10:30:00 AM,01/01/2019 10:30:00 AM,01/01/2023 10:30:00 AM,01/01/2025 10:30:00 AM
ABC000017	CustomerContract17	-1,0	02/15/2019 10:30:00 AM,01/01/2020 10:30:00 AM
ABC000018	CustomerContract18	-1,0	02/15/2019 10:30:00 AM,01/01/2020 10:30:00 AM
ABC000019	CustomerContract19	-1,0	02/15/2019 10:30:00 AM,01/01/2020 10:30:00 AM
ABC000020	CustomerContract20	-1,0	02/15/2019 10:30:00 AM,01/01/2020 10:30:00 AM
ABC000021	CustomerContract21	-1,0	02/15/2019 10:30:00 AM,01/01/2020 10:30:00 AM
ABC000022	CustomerContract22	-1,0	02/15/2019 10:30:00 AM,01/01/2020 10:30:00 AM
ABC000023	CustomerContract23	1.5,3,5,6,7	01/01/2008 10:30:00 AM,01/01/2009 10:30:00 AM,01/01/2019 10:30:00 AM,01/01/2023 10:30:00 AM,01/01/2025 10:30:00 AM
ABC000024	CustomerContract24	-5,-3.5,-2.5,-1.5,0	12/31/2002 11:30:00 AM,01/01/2007 11:30:00 AM,01/01/2008 11:30:00 AM,01/01/2009 11:30:00 AM,01/01/2010 11:30:00 AM

[wdx223_Daniel · Accepted solution]

NewStep=#table(Table.ColumnNames(YourTableName),List.TransformMany(Table.ToRows(YourTableName),each List.Zip(List.Transform(List.Skip(_,2),each Text.Split(_,","))),(x,y)=>List.FirstN(x,2)&y))

[Omid_Motamedise · Accepted solution]

Thanks @CaptOdyssey 

It is now more sensible now,

please use the below code which Source is related to the table you presented here.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Source,{"RateDifferentialPercentages", "RateDiffEffectiveDTs"},each List.Zip(List.Transform(_, (x)=> Text.Split(x,","))),"Merged"),
    #"Expanded Merged" = Table.ExpandListColumn(#"Merged Columns", "Merged"),
    Custom1 = Table.SplitColumn(#"Expanded Merged","Merged", each _)
in
    Custom1

If you have any question, just ask me 

[Anonymous]

Hi @CaptOdyssey ,

After testing, it seems that Omid_Motamedise's method can solve your problem very well. If his answer is helpful to you, please don't forget to accept it as a solution to help more others facing the same problem to find a solution quickly, thank you very much!

Best Regards,
Dino Tao

[Omid_Motamedise]

What is your desired result?

[CaptOdyssey]

The desired result would look something like this 

tract_No	Title	RateDifferentialPercentages	RateDiffEffectiveDTs
ABC000001	CustomerContract1	-3	01/12/2018 10:30:00 AM
ABC000001	CustomerContract1	0	01/01/2019 10:30:00 AM
ABC000002	CustomerContract2	2	01/01/2023 10:30:00 AM
ABC000002	CustomerContract2	0	01/01/2025 10:30:00 AM

[Omid_Motamedise · Accepted solution]

Thanks @CaptOdyssey 

It is now more sensible now,

please use the below code which Source is related to the table you presented here.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Source,{"RateDifferentialPercentages", "RateDiffEffectiveDTs"},each List.Zip(List.Transform(_, (x)=> Text.Split(x,","))),"Merged"),
    #"Expanded Merged" = Table.ExpandListColumn(#"Merged Columns", "Merged"),
    Custom1 = Table.SplitColumn(#"Expanded Merged","Merged", each _)
in
    Custom1

If you have any question, just ask me 

[CaptOdyssey]

Omid, thank you for the response. I think I understand most of what you have proposed. Can you explain how "List.Transform(_, (x)=> Text.Split(x,","))" works?

[wdx223_Daniel · Accepted solution]

NewStep=#table(Table.ColumnNames(YourTableName),List.TransformMany(Table.ToRows(YourTableName),each List.Zip(List.Transform(List.Skip(_,2),each Text.Split(_,","))),(x,y)=>List.FirstN(x,2)&y))

[CaptOdyssey]

Greetings wdx223_Daniel. I parsed your solution, figured out how it needed to fit into my code and voila it worked and produced the results needed. Thank you. 

[CaptOdyssey · Accepted solution]

Hello and thank you for your proposed solution. Unfortunately, I cannot figure out how to apply your suggestion. How would I add your suggested new step to the code below?

let
   Source = Table.Combine({RateDifferentials2}),
   #"Filtered Rows" = Table.SelectRows(Source, each [StdRateDiffEffectiveDTs] <> null and [StdRateDiffEffectiveDTs] <> ""),
   #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"StdRateDiffEffectiveDTs", type text}}),

[wdx223_Daniel]

let
   Source = Table.Combine({RateDifferentials2}),
   #"Filtered Rows" = Table.SelectRows(Source, each [StdRateDiffEffectiveDTs] <> null and [StdRateDiffEffectiveDTs] <> ""),
   #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"StdRateDiffEffectiveDTs", type text}}),

NewStep=#table(Table.ColumnNames(#"Changed Type"),List.TransformMany(Table.ToRows(#"Changed Type"),each List.Zip(List.Transform(List.Skip(_,2),each Text.Split(_,","))),(x,y)=>List.FirstN(x,2)&y))

in

NewStep