Forum Discussion
Extracting and Flattening an embedded table within a data set
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 |
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))
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 Custom1If you have any question, just ask me
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}}),
9 Replies
- wdx223_Daniel
Community Champion
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))
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
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.
- Omid_Motamedise
Super User
What is your desired result?
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
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 Custom1If you have any question, just ask me
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
