Forum Discussion

BE50's avatar
BE50
Frequent Visitor
4 years ago
Solved

Compare 2 datasets (budget vs realized, quotation vs project etc)

I am building a delivery validator for a large contractor. They send in a quotation with x items (could be work or material). During building process and after delivery they need to validate if they ...
Tutorial Requests
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    Code for table named Project

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxMFTSUXJMTARRkYZKsTpgQSOIIIiKNIIJGkMEQVSkMVTQyACq0gCs1AAmDDUVTEcCydhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IDX = _t, InfoA = _t, InfoY = _t])
in
    Source

     Code for table named Quotation

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxMFTSUXJMTARREYZKsTpgQSOIIIiKMIIJGkMEQVSEMVTQ0ACq3QCs3wAmDDMVYizQ3FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IDX = _t, InfoA = _t, InfoX = _t]),
    #"Merged Queries" = Table.NestedJoin(Source, {"IDX"}, Project, {"IDX"}, "Project", JoinKind.LeftOuter),
    #"Expanded Project" = Table.ExpandTableColumn(#"Merged Queries", "Project", {"InfoY"}, {"InfoY"}),
    Custom1 = Table.NestedJoin(Source, {"IDX"}, Project, {"IDX"}, "Project", JoinKind.RightAnti),
    #"Removed Columns" = Table.RemoveColumns(Custom1,{"IDX", "InfoA"}),
    #"Expanded Project1" = Table.ExpandTableColumn(#"Removed Columns", "Project", {"IDX", "InfoA", "InfoY"}, {"IDX", "InfoA", "InfoY"}),
    #"Appended Query" = Table.Combine({#"Expanded Project", #"Expanded Project1"})
in
    #"Appended Query"

     

BE50's avatar
BE50
Frequent Visitor
4 years ago

Thank you to Vijay. Your solution is very elegant. I merge queries all the time, but never thought of using a step in same query as source ðŸŒŸ 🌟 🌟

I came up with this solution:

1) Load each source separate (query only): qQuote, qProject

2) Rename columns so they have distinct names (Q_Unit, P_Unit, Q_Amount, P_Amount etc.)

3) Add IDX column in each cource

4) New qIDX query: Merge the 2, remove other columns, remove duplicates. I now have a combined index.

5) Merge qIDX and qQuote. Second merge with qProject. Reorder columns.

 

But I will try your approach also ðŸ˜€