Fill missing data in other columns of the table based on criteria in Product Name

[wsMac78AZ]

Hi,

I have a problem trying to ensure my table is consistent. Due to manual data entry in the source system, my table is inconsistent. How can I correct this in Power BI? 

Thanking you in advance.

Regards,

[Ashish_Mathur]

Hi,

Show the expected result clearly.  Also, share data in a format that can be pasted in an MS Excel file.

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZAxDwIhDIX/CmG+gcJxcKODo8kljoTBwUQG7xInf76tFOTQwaQJod/r49EQJMhBpuW2rVcBWtzTmrBxAKUIjF7JOASpWxFzOq3L3OxMlseGd6wJMh57LE6XJ9sQm/gZ2+gMc+q94dTCErRAt4M1gOcAvsccgCQ2S+aP5Hzkt+dijzZ/bAmgW1Md193HsJzmIfMjOjm7Ov21PlaYqrBdeCyDzRhf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ref:" = _t, #"Product Name" = _t, #"Product ID" = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ref:", Int64.Type}, {"Product Name", type text}, {"Product ID", type text}, {"Value", Int64.Type}}),
    ProductTbl = Table.SelectRows(#"Changed Type", each ([Product ID] <> "" and [Product ID] <> null)),
    ProductTblUnique = Table.Distinct(ProductTbl, {"Product Name"}),
    ValueTbl = Table.SelectRows(#"Changed Type", each ([Value] <> "" and [Value] <> null )),
    ValueTblUnique = Table.Distinct(ValueTbl, {"Product Name"}),
    UpdateProductID = Table.ReplaceValue(#"Changed Type",each [Product ID],each if [Product ID]=null or [Product ID]="" then try ProductTblUnique{[Product Name=[Product Name]]}[Product ID] otherwise [Product ID] else [Product ID],Replacer.ReplaceValue,{"Product ID"}),
    UpdateValue = Table.ReplaceValue(UpdateProductID,each [Value],each if [Value]=null or [Value]="" then try ValueTblUnique{[Product Name=[Product Name]]}[Value] otherwise [Value] else [Value],Replacer.ReplaceValue,{"Value"})
in
    UpdateValue

[wsMac78AZ]

Hi Vijay,

Many thanks for the solution you gave. I almost resolved everything but I noticed that there are 2 items still missing and they are Ref: 6 & 8. I wonder why given the gaps can be filled by information from Ref 15 & Ref 24 for iPhone 13 mini while Ref 26 for iPhone 13 Pro Max has the information on Product ID that can be used for the gap in Ref 8. 

Any idea how this can be resolved? Many thanks!

[Vijay_A_Verma · Accepted solution]

Because my sample data doesn't contain data for those. Once you use my code for your dataset, all will be filled. 

Just replace Source line with your dataset. The intention was to demonstrate the steps so that you can put in your data. 

So, when you import your data in PQ, you will notice a Source line is generated. Copy that Source and replace Source line in my code, then you will have all data from your source filled in properly. 

[wsMac78AZ]

Thank you so much for your help Vijay. It has now solved my problem.