Results 1 to 1 of 1

Thread: PQ - Use table to edit values of another table in Power Query

  1. #1
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    236
    Rep Power
    7

    Cool PQ - Use table to edit values of another table in Power Query

    (question from the web)
    How do we update values in Table A using the Values of Table B and keep the rest as they are?
    TABLE A TABLE B Result
    SiteId date Value 1 Value 2 Value 3 SiteId date Value2 SiteId date Value 1 Value 2 Value 3
    1
    01/01/2020
    5
    20
    500
    1
    01/01/2020
    25
    1
    01/01/2020
    5
    25
    500
    1
    02/05/2020
    6
    60
    100
    3
    01/01/2021
    73
    1
    02/05/2020
    6
    60
    100
    3
    01/01/2021
    2
    85
    250
    3
    01/01/2021
    2
    73
    250
    5
    01/01/2020
    6
    14
    566
    5
    01/01/2020
    6
    14
    566

    Code:
    let
        SourceA = Excel.CurrentWorkbook(){[Name="TableA"]}[Content],
        SourceB = Excel.CurrentWorkbook(){[Name="TableB"]}[Content],
        Join = Table.NestedJoin(SourceA, {"SiteId", "date"}, SourceB,{"SiteId", "date"}, "Table", JoinKind.LeftOuter),
        Expand = Table.ExpandTableColumn(Join, "Table", {"Value2"}, {"Value2"}),
        IF = Table.AddColumn(Expand, "Custom", each if [Value2] = null then [Value 2] else [Value2]),
        TSC = Table.SelectColumns(IF,{"SiteId", "date", "Value 1", "Custom", "Value 3"}),
        Ren = Table.RenameColumns(TSC,{{"Custom", "Value 2"}}),
        Type = Table.TransformColumnTypes(Ren,{{"date", type date}})
    in
        Type
    Last edited by sandy666; 03-08-2021 at 03:37 AM.
    sandy
    I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt

Similar Threads

  1. PQ - Choose power query table destination range & sheet
    By sandy666 in forum Power Query, Power Pivot and Power BI
    Replies: 0
    Last Post: 03-02-2021, 02:01 AM
  2. PQ - Power Query: search multiple columns against a list of values?
    By sandy666 in forum Power Query, Power Pivot and Power BI
    Replies: 0
    Last Post: 02-22-2021, 11:50 PM
  3. PQ - How to get Total via Power Query
    By sandy666 in forum ETL PQ Tips and Tricks
    Replies: 0
    Last Post: 09-23-2020, 06:10 PM
  4. Make Table SQL Query In Access That Adds A Primary Key
    By Portucale in forum Access Help
    Replies: 5
    Last Post: 08-08-2013, 10:02 PM
  5. Conditional Insert Query Based on Data in Target Table
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 06-23-2013, 04:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •