Forum Discussion

CaptOdyssey's avatar
CaptOdyssey
Regular Visitor
1 year ago
Solved

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

 

Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    1 year ago

    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's avatar
    Omid_Motamedise
    1 year ago

    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's avatar
    CaptOdyssey
    1 year ago

    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's avatar
    wdx223_Daniel
    Community Champion
    1 year ago

    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's avatar
      CaptOdyssey
      Regular Visitor
      1 year ago

      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's avatar
        wdx223_Daniel
        Community Champion
        1 year ago

        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

    • CaptOdyssey's avatar
      CaptOdyssey
      Regular Visitor
      1 year ago

      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's avatar
      CaptOdyssey
      Regular Visitor
      1 year ago

      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's avatar
        Omid_Motamedise
        Super User
        1 year ago

        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's avatar
    Anonymous
    Not applicable
    1 year ago

    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