How can I combine my data to one row per item

[JoanCampbell]

I need my data to be on one row per item, Any ideas on how I can combine

[v-xiaotang · Accepted solution]

Hi @JoanCampbell 

It's easy to get by using Fill in Power Query Editor, try this

(1) Replace 0 with null

(2) Fill Up/Down

(3) Remove duplicates

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvP3d1HwLEnNVVBWMFHSUQIiAyA2UorVwSppBFaATVLFFKoXKB0LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Currency.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",0,null,Replacer.ReplaceValue,{"Column3"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",0,null,Replacer.ReplaceValue,{"Column4"}),
    #"Filled Up" = Table.FillUp(#"Replaced Value1",{"Column2", "Column3", "Column4"}),
    #"Filled Down" = Table.FillDown(#"Filled Up",{"Column2", "Column3", "Column4"}),
    #"Removed Duplicates" = Table.Distinct(#"Filled Down")
in
    #"Removed Duplicates"

Best Regards,

Community Support Team _Tang

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

[Greg_Deckler]

@JoanCampbell So in Power Query you could do a Group By and maybe a max aggregation for each column. Otherwise if you did a table visualization you could create a measure for each column that did a MAX and it should automatically only show a single row for each distinct Item.