Transformation help please?

[MT_1909]

Hi Everyone,

I have a worksheet with 50k+ rows and would like to create a lineage using power query but I can't seem to get it right.

Can someone please help? Thank you so much in advance!

Here's a sample of my data:

Source DB	Source Table	Source Column	Target DB	Target Table	Target Column
S_DB1	S_Table1	S_Column1	T_DB1	T_Table1	T_Column1
T_DB1	T_Table1	T_Column1	T_DB2	T_Table2	T_Column2
T_DB2	T_Table2	T_Column2	T_DB4	T_Table4	T_Column4
T_DB4	T_Table4	T_Column4	T_DB6	T_Table6	T_Column6
S_DB3	S_Table3	S_Column3	T_DB3	T_Table3	T_Column3
T_DB3	T_Table3	T_Column3	T_DB7	T_Table7	T_Column7
T_DB7	T_Table7	T_Column7	T_DB9	T_Table9	T_Column9

My desired outcome is ...

Source DB

Source Table

Source Column

Target DB

Target Table

Target Column

Target DB Hop 1

Target Table Hop 1

Target Column Hop 1

Target DB Hop 2

Target Table Hop 2

Target Column Hop 2

Target DB Hop 3

Target Table Hop 3

Target Column Hop 3

S_DB1

S_Table1

S_Column1

T_DB1

T_Table1

T_Column1

T_DB2

T_Table2

T_Column2

T_DB4

T_Table4

T_Column4

T_DB6

T_Table6

T_Column6

S_DB3

S_Table3

S_Column3

T_DB3

T_Table3

T_Column3

T_DB7

T_Table7

T_Column7

T_DB9

T_Table9

T_Column9

[wdx223_Daniel]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZDBCoAgDIbfxXOXnCheqzfIm0gUdLM69f5li20dDATHvv8bYzGqcRq6VjX3H+Ylr1j2Rz63vdThxYFxIJya+B9AXzPWAmvyqwH0DWMjsCG/GkDfMrYC28e/G59XeuUmwDcBcRN4ZwLPBDETaKdqAH3H2AnsyK8G0PeMvcBepXQB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Source DB" = _t, #"Source Table" = _t, #"Source Column" = _t, #"Target DB" = _t, #"Target Table" = _t, #"Target Column" = _t]),
    Custom1 = Table.Combine(
                            Table.Group(
                                        Source,
                                        "Source DB",
                                        {
                                         "n",
                                         each let
                                                 a=List.Distinct(
                                                                 List.Split(
                                                                            List.Combine(
                                                                                         Table.ToRows(
                                                                                                      Table.Skip(
                                                                                                                 _,
                                                                                                                 each [Source DB]="" or [Source DB]=null
                                                                                                                )
                                                                                                     )
                                                                                        ),
                                                                            3)
                                                                )
                                              in
                                                 #table(
                                                        Table.ColumnNames(_)&
                                                        List.TransformMany(
                                                                           {1..List.Count(a)-2},
                                                                           each List.LastN(Table.ColumnNames(Source),3),
                                                                           (x,y)=>y&" Hop "&Text.From(x)
                                                                          ),
                                                        {List.Combine(a)}
                                                       )
                                        },
                                        0,
                                        (x,y)=>Byte.From(y=null or y="")
                                       )
                           [n])
in
    Custom1

[MT_1909]

Hi @wdx223_Daniel,

Thank you so much for the code, I've tested it and it worked, perfectly what I'm looking for.

I would like to test the code using my xls file but am having trouble in how to ...

replace this ...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZDBCoAgDIbfxXOXnCheqzfIm0gUdLM69f5li20dDATHvv8bYzGqcRq6VjX3H+Ylr1j2Rz63vdThxYFxIJya+B9AXzPWAmvyqwH0DWMjsCG/GkDfMrYC28e/G59XeuUmwDcBcRN4ZwLPBDETaKdqAH3H2AnsyK8G0PeMvcBepXQB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Source DB" = _t, #"Source Table" = _t, #"Source Column" = _t, #"Target DB" = _t, #"Target Table" = _t, #"Target Column" = _t]),

to point to an xls file ...

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
in
Source

Thank you so much again and am looking forward to your response 🙂

[wdx223_Daniel]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = Table.Combine(
                            Table.Group(
                                        Source,
                                        "Source DB",
                                        {
                                         "n",
                                         each let
                                                 a=List.Distinct(
                                                                 List.Split(
                                                                            List.Combine(
                                                                                         Table.ToRows(
                                                                                                      Table.Skip(
                                                                                                                 _,
                                                                                                                 each [Source DB]="" or [Source DB]=null
                                                                                                                )
                                                                                                     )
                                                                                        ),
                                                                            3)
                                                                )
                                              in
                                                 #table(
                                                        Table.ColumnNames(_)&
                                                        List.TransformMany(
                                                                           {1..List.Count(a)-2},
                                                                           each List.LastN(Table.ColumnNames(Source),3),
                                                                           (x,y)=>y&" Hop "&Text.From(x)
                                                                          ),
                                                        {List.Combine(a)}
                                                       )
                                        },
                                        0,
                                        (x,y)=>Byte.From(y=null or y="")
                                       )
                           [n])
in
    Custom1

[m_dekorte]

Hi @MT_1909 ,

Just replace the full Source step with your Source step but don't forget to add a comma at the end of the line 😉 so it should look like this:

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

I hope this is helpful