I think this surely must be a simple thing to achieve, but I have tried various appends and merges and can't seem to get it right.

I have two files, one titled 'Previous' and one titled 'Current'. Both show near identical data, like so :

ID    Status    Date_Changed
1     Closed    10/11/21
2     Open      10/01/21
3     Closed    10/03/21
4     Pending   10/15/21

I'd like to merge both files together, but retain all columns so that it is structured as below. This will allow me to show tables of what has changed etc.

ID    Previous.Status    Current.Status    Previous.Date_Changed    Current.Date_Changed
1     Closed             Open              10/11/21                 10/15/21
2     Open               Closed            10/01/21                 10/15/21
3     Closed             Pending           10/03/21                 10/14/21

I am aware this is probably due to my own naivety with PowerBI. I have tried combining the data by connecting to the folder, but that seems to create a new dataset with the data stacked on top (ie with duplicate ID values). I tried using merge queries as new and joiningby ID, but that didn't seem to give me the right output either?

CodePudding user response：

You can start from the Current table and merge in the previous table joining on ID and then expand the columns. Rename and reorder columns as desired.

Here's an example you can paste into the Advanced Editor:

let
    CurrentSource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIvSM0DUoYG oam kYGRoZKsTrRSkZAIeec/OLUFEw5Y6BQQGpeSmZeOlTSBCFpglMyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Status = _t, Date_Changed = _t]),
    Current = Table.TransformColumnTypes(CurrentSource,{{"ID", Int64.Type}, {"Status", type text}, {"Date_Changed", type date}}),
    PreviousSource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLOyS9OTQEyDA30DQ31jQyMDJVidaKVjIBC/gWpeRAZAyQZYzRdBsYIOROgUEBqXkpmXjrUSFOoZCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Status = _t, Date_Changed = _t]),
    Previous = Table.TransformColumnTypes(PreviousSource,{{"ID", Int64.Type}, {"Status", type text}, {"Date_Changed", type date}}),
    #"Merged Queries" = Table.NestedJoin(Current, {"ID"}, Previous, {"ID"}, "Previous", JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Status", "Date_Changed"}, {"Previous.Status", "Previous.Date_Changed"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Previous",{{"Status", "Current.Status"}, {"Date_Changed", "Current.Date_Changed"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"ID", "Previous.Status", "Current.Status", "Previous.Date_Changed", "Current.Date_Changed"})
in
    #"Reordered Columns"

Note: I've defined Previous within the query above so that it's self-contained. Ordinarily, it would be a separate query.

CodePudding user response：

You can try the following steps:

1. create a new column called "Source Name" in each table with a constant value mentioning if the data if from "Previous" or "Current"

2. Then append both the table in Power Query. This will still stack both the tables on top of each other.

3. But now you can use the "Source Name" column to differentiate them in the Matric Visual. You can add a Matrix visual with

   • "Source Name" in the Column Field
   • "Date" & "Status" in values field
   • "ID" in Rows field

This is ideal because you get all the data in a tabular format which will help you in further calculations if necessary

You can check out an example screenshot below: