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_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/...
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.

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)