Forum Discussion

sarah2's avatar
sarah2
Helper II
2 years ago
Solved

Separate workbook to guide dynamic changes

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 IDBusinessStaffQuestion 1 Question 2
101FranceTeam AYItem 2
103FranceTeam AYItem 2
104FranceTeam AYItem 2
189FranceTeam AYItem 2

 

Proposed "Edit Table"

Session ID BusinessStaffQuestion 1Question 2
103DELETE    
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 IDBusinessStaffQuestion 1Question 2
101FranceTeam AYItem 2
104ItalyTeam AYItem 2
189FranceTeam CYItem 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!

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

6 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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's avatar
      sarah2
      Helper II
      1 year ago

      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's avatar
        dufoq3
        Community Champion
        1 year ago

        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.