Forum Discussion
Need Help! Data transformation
Thanks Anonymous and smpa01 for your help and solution.
Your solution works well in most of the cases. But I have few records where COL2 and COL3 values are not in order same as COL1.
For Example:
| ID | COL1 | COL1_Value | COL2 | COL2_Value | COL3 | COL3_Value |
| 1 | ServerName | ABCDEF | DiskName | E | ServerName | QWERTY |
| 2 | DiskName | D | ServerName | POIUYT | DiskName | F |
| 3 | Application | LKJ | Application | HYU |
Could you please check if this scenerio could also be handled.
Thanks,
Randhir
HI, Anonymous
You may try this way:
Duplicate the basic table twice.
Then remove COL1 and COL1_Value for first table, and remove COL2 and COL2_Value for second table, and remove COL3 and COL3_Value for yhird table
Then merge table1 with table2 by ID and COL column, expend then merge with table3 again by the same logic.
Now we get the COL2 and COL3 values are in order same as COL1
Then transform it as above.
Best Regards,
Lin
Hi @v-lili6-msft , Thanks for your help. I have total total 40 columns ( COL1,COL_Value, COL2,COL2_Value.... COL20, COL20_Value) .
I LIKE your approach. It is simple. Do you think it would be good to create 20 tables from performance and storage point of view?
Can we handle of this manual approach using M query? Like
SelectColumnFromTable1 = Table.SelectColumns(#"Changed Type",{"COL1","COL1_Value"}), SelectColumnFromTable2 = Table.SelectColumns(#"Changed Type_",{"COL2","COL2_Value"}),
SelectColumnFromTable3 = Table.SelectColumns(#"Changed Type_",{"COL3","COL3_Value"}), #"Appended Query" = Table.Combine({SelectColumnFromTable1 , SelectColumnFromTable2, SelectColumnFromTable3 })And then PIVOT on "Appended Query"
Please advice on this.
Thanks,
Randhir Singh
hi, Anonymous
You could close "Enable load" for other queries.
For example:
Then they won't load in data model.
Best Regards,
Lin