Forum Discussion
Separate workbook to guide dynamic changes
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
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.
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?