Forum Discussion

MT_1909's avatar
MT_1909
Copper Contributor
Oct 30, 2023

Data transformation using Power Query?

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   
BI & Data Analysis
excel
Formulas and Functions
power bi
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Oct 31, 2023

    Hi MT_1909 

     

    Check the attached Power Query. It seems to do it although there's something I really don't understand in what you expect vs. your source data. So, if that query doesn't do it you will have to explain the logic to apply

    Note that I kept a blank row between each record as you seem to expect - easy to remove of course...

    • MT_1909's avatar
      MT_1909
      Copper Contributor
      Oct 31, 2023

      Hi Lorenzo 

       

      I used the code in your file but am getting an expression error: "There weren't enough elements in the enumeration to complete the operation."

      Details:

          [List]

       

      The only change I made was the Source = myFilename (instead of SourceTable) in the Linage table.

       

      The blank rows are not needed, I just added for ease of reading the data.

       

      My goal is to see the data in 1 row/multiple columns for each Source Column, instead of multiple rows.

       

      Can you please let me know what changes are needed to get your code to work?

       

      Thanks again!

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Nov 01, 2023

        Hi MT_1909 

         

        What changes are needed to get your code to work?
        It works (at least it doesn't raise Errors) with the sample (copy/paste of what you shared + a couple of other records I added for testing) in the workbook I shared. IMHO you go a bit too fast if I may. You know what you have (your actual data) and know +/- exactly what you expect. On the other hand helpers on forums have to understand... And until they get the right understanding + some representative actuals chances are high that they shoot in the dark

         

        #1 My goal is to see the data in 1 row/multiple columns for each Source Column
        In principle I understand what you want to do but let's confirm with a single set of records in A1:F5 below:

         

         

        #1.1 The Source Column you talk about is the one highlighted in dark orange (col. C) ???
        #1.2 Data in A3:C3 are "ignored"/shouldn't be reported ???
        #1.3 Expected result in A1:O2 ???
        #1.4 A few other ??? in attached file sheet Source

         

        #2 "There weren't enough elements in the enumeration to complete the operation."
        Many possibilities. I tried a few scenarios but none failed with that error so it's hihgly probable your actual data are not consistently structured as what you initially shared and/or exceptions exist in the dataset. Attach (if not allowed share with OneDrive, Google Drive... and post the shared link) a representative workbook that raises the error please

         

        #3 The blank rows are not needed
        OK, revised accordingly

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Oct 31, 2023

    MT_1909 

     

    how about sql?

    create temp table aa as 
select fillna(iif(rowid=1 or length(f01)<2,rowid,'')) grp,* from 基础堆叠转二维;
select * from aa;
select colIndex[0:3],group_concat(f04||'</td><td>'||f05||'</td><td>'||f06,'</td><td>') detail from aa where length(f01)>2 group by grp;

     

     

    • MT_1909's avatar
      MT_1909
      Copper Contributor
      Oct 31, 2023
      Hi peiyezhu,

      I don't think I can use sql in power query or am I wrong?

      Also no need for the blank rows.

      Please let me know if you know of a solution using the Power Query UI?

      Thank you!
      • peiyezhu's avatar
        peiyezhu
        Bronze Contributor
        Oct 31, 2023

        MT_1909 

        I don't think I can use sql in power query or am I wrong?

        You are right.

        You can not use sql in power query.

        It is online sql.

         

        https://b23.tv/wEH3Fev

         

         

        Please let me know if you know of a solution using the Power Query UI?

        Sorry,I do not konw any solution using the Power Query.

    • peiyezhu's avatar
      peiyezhu
      Bronze Contributor
      Oct 31, 2023

       

      peiyezhu 

      add blank row.

       

       

       create temp table aa as 
select fillna(iif(rowid=1 or length(lag(f01) over())<2,rowid,'')) grp,iif(length(f01)<2,'g'||rowid,'') grp2,* from 基础堆叠转二维;
select * from aa;
select colIndex[2:4],group_concat(f04||'</td><td>'||f05||'</td><td>'||f06,'</td><td>') detail from aa group by grp,grp2;

       

       

       

       

       

      f01 f02 f03 detail

      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

Resources