Forum Discussion

im_92's avatar
im_92
Frequent Visitor
5 years ago
Solved

create new table with column values as rows

Hi all, 

 

I have a table in the following format: 

patient_iddateagedatapointvalue
11/03/202156BMI25.0
15/4/202156height182
21/1/202040BMI22.0
23/5/202040height163

 

I want to create a new table with the following format: 

patient_iddateageBMIheight
11/03/20215625.0 
15/4/202156 182
21/1/20204022.0 
23/5/202040 163

 

Is this possible with a rather simple query? 

I have already tried to create a matrix in the correct format & export it. The matrix looks exactly what I need, but after exporting, the datapoints are converted back to rows instead of columns. So I am trying to find another solution. 

 

Thanks!

 

BW, 

Imke

Need Help
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"patient_id", Int64.Type}, {"date", type datetime}, {"age", Int64.Type}, {"datapoint", type text}, {"value", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[datapoint]), "datapoint", "value")
in
    #"Pivoted Column"

    Hope this helps.

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"patient_id", Int64.Type}, {"date", type datetime}, {"age", Int64.Type}, {"datapoint", type text}, {"value", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[datapoint]), "datapoint", "value")
in
    #"Pivoted Column"

    Hope this helps.

    • im_92's avatar
      im_92
      Frequent Visitor
      5 years ago

      In practice, I have >100 datapoints, so I want to avoid creating it column per column. 
      Unpivoting in power query is definitely an option, but there the issue is that I have created calculated columns in the table which I now want to reuse in the unpivoted table (but these columns are not shown in power query) 

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hi im_92 ,

    Since there are > 100 fields in [datapoint] column, it is better to use unpivot to transform data in power query instead of creating calculated columns one by one.

    In addition, calculated column would not show in power query so if you want to use the calculation results in power query, you may need to create custom columns using M code to achieve the same result first.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.