How to merge rows so that there are no blanks

[kagy100]

Hi All,

Here is what the data looks like : Current State

Year	Target	Current Value	Percentage	Account
17/18		1.2651		Account A
18/19		1.357714467		Account A
18/19			-7.32 %	Account A
19/20		0.72		Account A
19/20			46.97 %	Account A
20/21				Account A

This is what I want the data to look like: Desired State

Year	Target	Current Value	Percentage	Account
17/18		1.2651		Account A
18/19		1.357714467	-7.32 %	Account A
19/20		0.72	46.97 %	Account A
20/21				Account A

How do I merge the rows so I have all data for the year in one row.

Thanks

[AllisonKennedy · Accepted solution]

@kagy100 

Are you always wanting the maximum value? Is there ever any difference in text that needs reconciled/merged? 

I would suggest using Power Query for this - so click Transform Data. 

If all the values with blanks are numeric, then you can simply use a MAX aggregation, if they are text, we will need to add a step first. Click "Group By", select the 'Advanced' option. 

Choose Date for your first Group by column. Add Grouping> choose Account for your second Group by column. 

Then you will need to add a column for every merged value that you want, so in the sample you provided, choose as in the image below: 

Or paste this code into Advanced Editor to see it work: 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hcy9CoAgFIbhWxGhzdRz/Dk6eh3i5F5L3X8KElFCwzt88PDlzIEUBC44a4FE72CMVOt+bgdLvIjGgoJ4M+OIwFpPv7a3kjTIlq+LCvUwWhLOzx6oZ72MNDlDrRAe7vVULg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Target = _t, #"Current Value" = _t, Percentage = _t, Account = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", type text}, {"Target", type number}, {"Current Value", type number}, {"Percentage", Percentage.Type}, {"Account", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Year", "Account"}, {{"Target", each List.Max([Target]), type nullable text}, {"Percentage", each List.Max([Percentage]), type nullable number}, {"Current Value", each List.Max([Current Value]), type nullable number}})
in
#"Grouped Rows"

[kagy100]

Thank You @AllisonKennedy I followed your instructions and I was able to achieve my desired result.

[AllisonKennedy]

You're welcome! Glad you got what you needed. 

[amitchandak]

@kagy100 , You have aggregate in power Query

https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table

Or use summarize in DAX , Create new table

Summarize(Table,Table[Year],"Target",Min(Table[Target]),"Current Value", min(Table[Current Value]),"Percentage",min(Table[Percentage]),"Account",min(Table[Account]))

or

Summarize(Table,Table[Year],"Target",Max(Table[Target]),"Current Value", Max(Table[Current Value]),"Percentage",max(Table[Percentage]),"Account",max(Table[Account]))

[kagy100]

Thank You for taking the time to assist me with the data query. I tried the Group By Function in the Query Editor and was able to achieve the desired output. I will keepy your DAX technique in mind for future cases. 🙂