create new table with column values as rows

[im_92]

Hi all, 

I have a table in the following format: 

patient_id	date	age	datapoint	value
1	1/03/2021	56	BMI	25.0
1	5/4/2021	56	height	182
2	1/1/2020	40	BMI	22.0
2	3/5/2020	40	height	163

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

patient_id	date	age	BMI	height
1	1/03/2021	56	25.0
1	5/4/2021	56		182
2	1/1/2020	40	22.0
2	3/5/2020	40		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

[Ashish_Mathur · Accepted solution]

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.

[v-yingjl]

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.

[amitchandak]

@im_92 , Try Unpivot in power Query

https://radacad.com/pivot-and-unpivot-with-power-bi

Or new column in DAX

BMI = if([datapoint] ="BMI", [Value], Blank())

height= if([datapoint] ="height", [Value], Blank())

[im_92]

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)