Forum Discussion

MT_1909's avatar
MT_1909
New Member
2 years ago

Transformation help please?

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 DBSource TableSource ColumnTarget DBTarget TableTarget Column
S_DB1S_Table1S_Column1T_DB1T_Table1T_Column1
T_DB1T_Table1T_Column1T_DB2T_Table2T_Column2
T_DB2T_Table2T_Column2T_DB4T_Table4T_Column4
T_DB4T_Table4T_Column4T_DB6T_Table6T_Column6
      
S_DB3S_Table3S_Column3T_DB3T_Table3T_Column3
T_DB3T_Table3T_Column3T_DB7T_Table7T_Column7
T_DB7T_Table7T_Column7T_DB9T_Table9T_Column9

 

My desired outcome is ...

Source DBSource TableSource ColumnTarget DBTarget TableTarget ColumnTarget DB Hop 1Target Table Hop 1Target Column Hop 1Target DB Hop 2Target Table Hop 2Target Column Hop 2Target DB Hop 3Target Table Hop 3Target Column Hop 3
S_DB1S_Table1S_Column1T_DB1T_Table1T_Column1T_DB2T_Table2T_Column2T_DB4T_Table4T_Column4T_DB6T_Table6T_Column6
               
S_DB3S_Table3S_Column3T_DB3T_Table3T_Column3T_DB7T_Table7T_Column7T_DB9T_Table9T_Column9   
Need Help

4 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago
    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's avatar
      MT_1909
      New Member
      2 years ago

      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 🙂

      • m_dekorte's avatar
        m_dekorte
        Resident Rockstar
        2 years ago

        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