Power Query

[Ronak_Mistry]

Hello, 

I want fill all blank value in PartyType with respect to Code Coloum.

like.    Code = A0103 and Value of Party Type is d
this value fill everywhere when used code is equal to A0103.

Similary with Next Code.

I want this Output

[BA_Pete · Accepted solution]

Hi @Ronak_Mistry ,

1) Group your table on code and create one aggregate column using the 'All Rows' operator, and one using MAX of [Party Type].

2) Expand your nested table column back out to reinstate your original rows.

Example Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwNDBW0lFSitWJVqqoNDA2gnGSjE0MTBEyVUBmaiqYA9OTQpQmFC0oOhITSdeDoqOsDEVPLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, PartyType = _t]),
    groupAllRows = Table.Group(Source, {"Code"}, {{"data", each _, type table [Code=nullable text, PartyType=nullable text]}, {"maxPartyType", each List.Max([PartyType]), type nullable text}}),
    expandData = Table.ExpandTableColumn(groupAllRows, "data", {"PartyType"}, {"PartyType"})
in
    expandData

Example output:

Pete