Separate workbook to guide dynamic changes

[sarah2]

Hi there! I'm working with a data set that is downloaded, put in a folder and combined using power query. I don't want to edit any of the raw data because the documents will be replaced as more info is downloaded. We have to make changes when there are mistakes - editing values in certain columns or even deleting certain rows. My idea is to have a separate sheet that I can record those changes and have it replace the values when there's something on this sheet. 

Raw Data

Session ID	Business	Staff	Question 1	Question 2
101	France	Team A	Y	Item 2
103	France	Team A	Y	Item 2
104	France	Team A	Y	Item 2
189	France	Team A	Y	Item 2

Proposed "Edit Table"

Session ID		Business	Staff	Question 1	Question 2
103	DELETE
104		Italy
189			Team C		Item 3

*If the "delete" idea is impossible I'm fine with taking that part off and doing that manually in power query

End Result

Session ID	Business	Staff	Question 1	Question 2
101	France	Team A	Y	Item 2
104	Italy	Team A	Y	Item 2
189	France	Team C	Y	Item 3

I'm not sure what the most efficent way to do this is or if there's a better way then the "Edit Table" so I'd love to hear if you have a better idea!

[dufoq3 · Accepted solution]

Hi @sarah2, check this:

Just make sure the DELETE column name of Edit Table is "Delete"

Output

let
    T1RawData = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRcitKzEtOBTJCUhNzFRyBjEgg9ixJzVUwUorVASkzJk6ZCVHKLCwJKosFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Session ID" = _t, Business = _t, Staff = _t, #"Question 1 " = _t, #"Question 2" = _t]),
    T2EditTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVtJRcnH1cQ1xBTIUUHCsDkiBCZTvWZKYU4kpb2GJJBaSmpir4AzXkJqrYKwUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Session ID" = _t, Delete = _t, Business = _t, Staff = _t, #"Question 1" = _t, #"Question 2" = _t]),
    // You can probably delete this step when applying on real data.
    T2ReplaceBlankToNull = Table.TransformColumns(T2EditTable, {}, each if Text.Trim(_) = "" then null else _),
    MergedQueries = Table.NestedJoin(T1RawData, {"Session ID"}, T2ReplaceBlankToNull, {"Session ID"}, "T2", JoinKind.LeftOuter),
    Ad_Updated = Table.AddColumn(MergedQueries, "Updated", each 
        [ a = Record.FromTable(Table.SelectRows(Record.ToTable([T2]{0}?), (x)=> x[Value] <> null)),
          b = if Text.Upper([T2]{0}?[Delete]?) = "DELETE" then null else _ & (try a otherwise []),
          c = try Table.RemoveColumns(Table.FromRecords({b}), {"T2"}) otherwise null
        ][c], type table),
    CombinedUpdated = Table.Combine(List.RemoveNulls(Ad_Updated[Updated]), Value.Type(T1RawData))
in
    CombinedUpdated

[sarah2]

Hi, I finally had time to revisit this-- super appreicate your reply! I'm not really familiar with using code like this, but I followed your link instructions and I think I'm close to adapting it. Is there anything below the note "// You can probably delete this step when applying on real data." that needs to be changed? Both files are in the same Power BI document "Raw Data" and "Edits"

Here's my attempt at adapting it :

= let
T1RawData = Table.FromRows(#"Raw Data"), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"SessionID" = _t, #"Question: Temperature" = _t]),
T2EditTable = Table.FromRows(#"Edits"), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"SessionID" = _t, Delete = _t, #"Question: Temperature" = _t]),
// You can probably delete this step when applying on real data.
T2ReplaceBlankToNull = Table.TransformColumns(T2EditTable, {}, each if Text.Trim(_) = "" then null else _),
MergedQueries = Table.NestedJoin(T1RawData, {"SessionID"}, T2ReplaceBlankToNull, {"SessionID"}, "T2", JoinKind.LeftOuter),
Ad_Updated = Table.AddColumn(MergedQueries, "Updated", each
[ a = Record.FromTable(Table.SelectRows(Record.ToTable([T2]{0}?), (x)=> x[Value] <> null)),
b = if Text.Upper([T2]{0}?[Delete]?) = "DELETE" then null else _ & (try a otherwise []),
c = try Table.RemoveColumns(Table.FromRecords({b}), {"T2"}) otherwise null
][c], type table),
CombinedUpdated = Table.Combine(List.RemoveNulls(Ad_Updated[Updated]), Value.Type(T1RawData))
in
CombinedUpdated

@dufoq3 

[dufoq3]

Hi @sarah2, you haven't read my note carefuly: You have to replace WHOLE code of T1RawData and also WHOLE code of T2EditTable so try this:

let
    T1RawData = #"Raw Data",
    T2EditTable = Edits,
    // You can probably delete this step when applying on real data.
    T2ReplaceBlankToNull = Table.TransformColumns(T2EditTable, {}, each if Text.Trim(_) = "" then null else _),
    MergedQueries = Table.NestedJoin(T1RawData, {"Session ID"}, T2ReplaceBlankToNull, {"Session ID"}, "T2", JoinKind.LeftOuter),
    Ad_Updated = Table.AddColumn(MergedQueries, "Updated", each 
        [ a = Record.FromTable(Table.SelectRows(Record.ToTable([T2]{0}?), (x)=> x[Value] <> null)),
          b = if Text.Upper([T2]{0}?[Delete]?) = "DELETE" then null else _ & (try a otherwise []),
          c = try Table.RemoveColumns(Table.FromRecords({b}), {"T2"}) otherwise null
        ][c], type table),
    CombinedUpdated = Table.Combine(List.RemoveNulls(Ad_Updated[Updated]), Value.Type(T1RawData))
in
    CombinedUpdated

As I mentioned in a comment: you can probably delete T2ReplaceBlankToNull step because it replaces blank values to null (which you probably do not need with your real data) - try to delete it and you will see. 

[sarah2]

Hey! This is mostly working for me. I'm having a problem getting it to do the delete thing, and I would try a few changes but it's taking a super long time to load. My Raw Data is like 30MB and the Edits is 10KB but when it loads it says the edits is like 600MB. I expect it to take a little time since I have a large data set, but it's way longer than it should be. Is that a consequence of this code or would that be something on my end?

[dufoq3]

Hi, try this:

in T2EditTable step replace 

Edits

with

Table.Buffer(Edits)

[sarah2]

I tried that for both table steps but it still takes an hour to load/refresh. Is there a more efficent way to merge the tables?