Multiple rows of Data onto one line?

[Creative_tree88]

Hi all - I have the attached data (sample).  I need to separate out Height and Weight based on the Measure Date field, so I can then have just one row of data and a column for Height and column for Weight.

I've shown the sample of data I have on the left and what I'd like to see on the right.

Can you please help me?  Many thanks.

Sample Data 

[Anonymous]

Hi @Creative_tree88 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Creative_tree88]

Sample Data 

I have the attached (linked) data.  Left table shows what I have, right table what I need it to look like in Power Query.  

I need the height and weight to be on the same line as I then need to calculate BMI (Body Mass Index) of each customer.  However, the customer can occur multiple times but on different dates to get weighted and measured.  So, I need the data to be able to allow me to do this.

If there are any other creative ways anyone can work out for me, I'd appreciate it.   Many thanks!

[Ritaf1983]

Hi @Creative_tree88 
Please relate to my response in your previous post:
https://community.fabric.microsoft.com/t5/Desktop/Multiple-rows-of-Data-onto-one-line/m-p/4369653/hi...

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[muhammad_786_1]

Hy, 

You can get your result using a power query.

In the Power Query Editor, go to the Transform tab.
Select the Type column.
Click on Pivot Column in the toolbar.
In the Values Column section, choose the Result column.

Best Regards,
Muhammad Yousaf

If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

LinkedIn

[suparnababu8]

Hi @Creative_tree88 

You can achive your requirement by using Pivot Column option in Power Query Editor. 
This is your input 

You'll get output as mentioned below by using pivot column

You can download pbix file from here Download pbix file from here 

Thanks

[Creative_tree88]

@suparnababu8 I'm finding that in my actual data, because there are multiple instances of the same customer number, with different measure dates, it's somehow separating off the height and weight elements so have either height or weight, but not both.

Is there a way to just bring the height and weight data onto one line based on customer number and measure date?

[suparnababu8]

Hi @Creative_tree88  - could you please provide what is your expected output? So we will able to help you. Thanks

[Creative_tree88]

Sample Data 

I've attached sample data.  So, there are often multiple instances of the same customer, but they have been measured on different dates.  From the data I need to calculate BMI (Body Mass Index) from the height and weight.  I''ve left this BMI column blank in the sample data as not sure whether to calculate it in Power Query, or as DAX measure. 

Either way, I need to get height and weight for each customer instance on one line - for each of the measure dates.

The sample data hopefully explains it better than I can.  Many thanks.

[Ritaf1983]

Hi @Creative_tree88 
The unpivoted result seems exactly like in your example.

According to bmi you can calculate it as a measure :

BMI = SUMX('Table','Table'[Height]/'Table'[Weight])

And can with division in PQ / calculated column depends on your needs

[Creative_tree88]

@Ritaf1983 Can you take me through the steps of the pivot, please?  I want to make sure I'm doing it right after the last attempt caused issues with the result.  Thanks.

[Ritaf1983]

You have the steps recorded in in pq  :

Note that I also needed to deal with the date zone because in Israel we use a British format 
dd/mm/yy (maybe you will not need it)

I attaching again a pbix + I recorded the screens you can download the mp4 from here :
https://drive.google.com/file/d/1pCjvKdjFHGuJrLkkDepctC-wGfqd5FOD/view?usp=sharing

[Creative_tree88]

@Ritaf1983 That's great, many thanks.  It seems to work now.  When it comes to the BMI calculation column, how can I calculate BMI where there is a Height AND weight value, but just show something like 'Cannot calculate BMI' if there is only one or the other (height or weight) populated?

I'm finding that the data is not complete, so quite often I only have one or the other.  However, if I try and just pop a measure in, I lose all the other lines of data which cannot calculate BMI.

Many thanks!

[Ritaf1983 · Accepted solution]

HI @Creative_tree88 
If you want to get blank BMI in these cases just modify fotmula to :

BMI = SUMX('Table',DIVIDE('Table'[Height],'Table'[Weight]))

If it is something else please show the desired result.

Modified pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[ajohnso2]

You can just create 2 measures...

Height = 
CALCULATE(MAX(Result), ALL('Your Table'[Type]), 'Your Table'[Type] = "Height")

Weight = 
CALCULATE(MAX(Result), ALL('Your Table'[Type]), 'Your Table'[Type] = "Weight")

You can add customer No, Measure Date and your 2 new measures to a table/chart etc

[Ilya_K]

In Power Query select column Type and do Pivot based on Result column

[Ritaf1983]

Hi @Creative_tree88 
This is the standard matrix :

Just use a tabular layout 

The pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Creative_tree88]

@Ritaf1983 Thanks.  But I need to calculate BMI using Height and Weight, so was hoping to group the data  one one line in Power Query so I can then perform the calculation.  Any ideas?

[Ritaf1983]

Hi again @Creative_tree88 
Yes, you can pivot the table :

The updated pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.