Forum Discussion
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! ...
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;
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_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 |