Modifing data based on a correction table

[DanFromMontreal]

Hello dear Power BI community,

I've got a good challenge that I was unable to resolve and/or find the right approach to modify several data in a table.

I have a table (tblData) containing more than 3000 lines.

I was given a table containing the correction (tblCorrection) that I need to apply to my tblData (in red).

The final result should look like the tblDataFinal.

The way I see it is that I need to find in the tblCorrection where there is a correction (red) and find the intersection Column-Row in the tblData and do the replace.

If the cell is empty, there is NO correction to be made.

Easier said then done as I'm a rookie in Power Query.

Any advice how to approach this?

Thank you in advance.

RowIDCustomerCityAddressZipcodePhone

Row1			CC
Row5	AA			BB
Row8		DD
Row10			EE	FF	GG

RowIDCustomerCityAddressZipcodePhone

Row1	Customer-Row1	City-Row1	CC	Zipcode-Row1	Phone-Row1
Row2	Customer-Row2	City-Row2	Address-Row2	Zipcode-Row2	Phone-Row2
Row3	Customer-Row3	City-Row3	Address-Row3	Zipcode-Row3	Phone-Row3
Row4	Customer-Row4	City-Row4	Address-Row4	Zipcode-Row4	Phone-Row4
Row5	AA	City-Row5	Address-Row5	BB	Phone-Row5
Row6	Customer-Row6	City-Row6	Address-Row6	Zipcode-Row6	Phone-Row6
Row7	Customer-Row7	City-Row7	Address-Row7	Zipcode-Row7	Phone-Row7
Row8	Customer-Row8	DD	Address-Row8	Zipcode-Row8	Phone-Row8
Row9	Customer-Row9	City-Row9	Address-Row9	Zipcode-Row9	Phone-Row9
Row10	Customer-Row10	City-Row10	EE	FF	GG

[v-kkf-msft · Accepted solution]

Hi @DanFromMontreal ,

Please try the following code:

let

    #"Unpivot tblData" = Table.UnpivotOtherColumns(tblData, {"RowID"}, "Attribute", "Value"),
    #"Unpivot tblCorrection" = Table.UnpivotOtherColumns(tblCorrection, {"RowID"}, "Attribute", "Value"),
    Source = Table.NestedJoin(#"Unpivot tblData", {"RowID", "Attribute"}, #"Unpivot tblCorrection", {"RowID", "Attribute"}, "tblCorrection", JoinKind.LeftOuter),
    #"Expanded tblCorrection" = Table.ExpandTableColumn(Source, "tblCorrection", {"Value"}, {"tblCorrection.Value"}),
    #"Added Custom" = Table.AddColumn(#"Expanded tblCorrection", "Custom", each if [tblCorrection.Value] = " " or [tblCorrection.Value] = null then [Value] else [tblCorrection.Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value", "tblCorrection.Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom")
in
    #"Pivoted Column"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DanFromMontreal]

Wow @v-kkf-msft , it does the trick.

Now, I need to better understand each step and learn from it.

Thank you

[Anonymous]

this should be faster.

if you publish tables with fake data but with the size and structure of your tables, one can do some tests to find the best performing solution.

PS

The "optimal" strategy obviously depends on the size of the tables.
From how many columns and how many rows the tables typically have it derives whether it is convenient to make the transformations by rows or by columns, whether to merge or do lookups, etc ...

[Anonymous]

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdK7CsJAFIThd9k6wmbvW4ovIJaGVCZgCl0xEfHtBfHAzOlmmq/6h8Gc2rs3nTm81q3d5udO/rJ9ZO+n6Tmvq9zz8ri0aZZ7vLb7/4zdz3PKc+A59hx7Dj0nnleeB8+z59nz6HnxgvICeIG9wF5AL4gXlRfBi+xF9iJ6UbykvAReYi+xl9BL4mXlZfAye5m9jF4WryivgFfYK+wV9Ip4VXkVvMpeZa+iV8XrrQ7aYtFWJW1V05aitmYcvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RowID = _t, Customer = _t, City = _t, Addres = _t, Zipcode = _t, Phone = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"RowID", type text}, {"Customer", type text}, {"City", type text}, {"Addres", type text}, {"Zipcode", type text}, {"Phone", type text}}),
    #"Merge di query eseguito" = Table.NestedJoin(#"Modificato tipo", {"RowID"}, corr, {"RowID"}, "corr", JoinKind.LeftOuter),
    #"Tabella corr espansa" = Table.ExpandTableColumn(#"Merge di query eseguito", "corr", {"RowID", "Customer", "City", "Addres", "Zipcode", "Phone"}, {"RowID.1", "Customer.1", "City.1", "Addres.1", "Zipcode.1", "Phone.1"}),
    nrows=Table.RowCount(Origine),
    ncols=Table.ColumnCount(Origine),
    names=Table.ColumnNames(Origine),
    ttc=Table.ToColumns(#"Tabella corr espansa"),
    tr=List.Transform({0..ncols-1}, each List.Transform({0..nrows-1}, (r)=>ttc{_+6}{r}??ttc{_}{r})),
    tfc=Table.FromColumns(tr,names)
in
    tfc

[Anonymous]

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsovN1TSUYIgZ2coM1YHLGMKZDs6wqWdnJDkLCBiLi5waZiMoQFcyNUVSLi5AQl3d6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RowID = _t, Customer = _t, City = _t, Addres = _t, Zipcode = _t, Phone = _t]),
    #"Sostituito valore" = Table.ReplaceValue(Origine,"",null,Replacer.ReplaceValue,{"Customer", "City", "Addres", "Zipcode", "Phone"})
in
    #"Sostituito valore"

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdK7CsJAFIThd9k6wmbvW4ovIJaGVCZgCl0xEfHtBfHAzOlmmq/6h8Gc2rs3nTm81q3d5udO/rJ9ZO+n6Tmvq9zz8ri0aZZ7vLb7/4zdz3PKc+A59hx7Dj0nnleeB8+z59nz6HnxgvICeIG9wF5AL4gXlRfBi+xF9iJ6UbykvAReYi+xl9BL4mXlZfAye5m9jF4WryivgFfYK+wV9Ip4VXkVvMpeZa+iV8XrrQ7aYtFWJW1V05aitmYcvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RowID = _t, Customer = _t, City = _t, Addres = _t, Zipcode = _t, Phone = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"RowID", type text}, {"Customer", type text}, {"City", type text}, {"Addres", type text}, {"Zipcode", type text}, {"Phone", type text}}),
    ncols=Table.ColumnCount(#"Modificato tipo"),
    ttr=Table.TransformRows(#"Modificato tipo" , each Record.FromList(List.Transform({0..ncols-1}, (f)=> Record.FieldValues(corrtab{[RowID=[RowID]]}? ?? _){f} ?? Record.FieldValues(_){f}),Record.FieldNames(_)))
in
    Table.FromRecords(ttr)

[DanFromMontreal]

Goog morning @Anonymous ,

Thank you for answering to my post and sorry for the delay.

I have tried your solution but your last "let" is giving mr an error.  It doest not recognize "corrtab" in:

    ttr=Table.TransformRows(#"Modificato tipo" , each Record.FromList(List.Transform({0..ncols-1}, (f)=> Record.FieldValues(corrtab{[RowID=[RowID]]}? ?? _){f} ?? Record.FieldValues(_){f}),Record.FieldNames(_)))

I therefore cannot test your solution.

No error were found in the other 2 script.

Tried to understand the error to fix it but could not.

Also, thank you for your advice about loading a file.  Read the post and it is very insightful.

However, due to company's policy, the use of Dropbox, Google drive or other tool, it is strickly forbidden.

Thank you again for looking once more at your code to find the problem.

Merci beaucoup / grazie.