Unpivoting the Data in Azure Data Explorer

Question

Hi I have a data like mentioned on below

Date,Col1,Col2
2021-11-02,a,b
2021-11-02,c,d

Unpivoted data should look like mentioned below.

Date,ColumnName,ColumnValue
2021-11-02,Col1,a
2021-11-02,Col1,b
021-11-02,Col2,c
2021-11-02,Col2,d

The above functionality how we can achieve in Azure Data Explorer.

Important Note: Sometimes the number of columns may be high or sometimes its less
for example one day it is Date,Col1,Col2 another day Date,Col1,Col2,Col3,Col4 and another day Date,Col1,Col2,Col3 like this.

Accepted Answer

@ADX You can modify the query provided earlier as follows:

   TestTable  
   | extend p = pack_all()  
   | project Date, colData = bag_remove_keys(p, dynamic(['Date']))  
   | mv-expand colData  
   | mv-apply colData on (  
       extend ColumnName = tostring(bag_keys(colData)[0])  
       | project ColumnName, ColumnValue = colData[ColumnName]  
   )

This would produce the output in the expected format. Hope this helps. Do let us know if you have further questions.

----------

If an answer is helpful, please "Accept answer" and/or "Up-Vote" which might help other community members reading this thread.

Answer

@ADX Thanks for reaching out. It would have helped to know the exact schema of your data to know how you are currently accommodating variable number of columns.

Nonetheless, I've assumed a sample structure and worked up a query as follows:

Table with sample data:

Query:

   datatable(Date:datetime, colData:dynamic)  
   [  
       '2021-11-02', dynamic({ "Col1": "a", "Col2": "b" }),  
       '2021-11-03', dynamic({ "Col1": "c", "Col2": "d" }),  
       '2021-11-04', dynamic({ "Col1": "e", "Col2": "f", "Col3": "g" }),  
       '2021-11-05', dynamic({ "Col1": "h", "Col2": "i", "Col3": "j", "Col4": "k" }),  
   ]  
   | mv-expand colData  
   | mv-apply colData on (  
       extend ColumnName = tostring(bag_keys(colData)[0])  
       | project ColumnName, ColumnValue = colData[ColumnName]  
   )

Running the above query as is, you'd see the following output:

Please feel free to adapt this query as per your schema.

Hope this helps. Do let us know if you have further questions.

----------

If an answer is helpful, please "Accept answer" and/or "Up-Vote" which might help other community members reading this thread.

Share via

Unpivoting the Data in Azure Data Explorer

1 additional answer