Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Combining 2 tables into one

Hello,

 

I need your support for the next topic:

I have 2 initial tables:

Tab 1: "List of suppliers"

Tab 2: "List of audit performed" - Note: not all the suppliers from Tab 1 are included in Tab 2 - depends if the audit "Type X" was performed or not.

I want to find the best way to generate a third table based on the 2 previous one, which will represent, for each "Audit" type, the audit "Status" for each supplier "Name" in the Tab 1. If no "Status" found in Tab 2, write "Not done".

Here is a simplified representation:

 

 Thanks and best regards.

Need Help
Tips and Tricks

5 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 

    Duplicate the Supplier Table and do the transformation:

    You can download the file:HERE

     

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous Seems like you should use a Merge query in Power Query Editor

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello Greg,

       

      I'm not sure that i know how to do it.. Can you help with details please ?

       

      Thanks.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Create a new table

     

    NewTable = crossjoin(suppliers, Distinct(Audit[Audit type]))

     

    and add this new column
    new column = coalesce(maxx(filter(Audit, Audit[suppliers] =NewTable[suppliers] && Audit[Audit type] =NewTable[Audit type]),[Audit Type]),"Not Done")

     

    You can merge in power query too-https://radacad.com/append-vs-merge-in-power-bi-and-power-query

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous 

    you can do it in Power Query like this:

     

     

    // Audit list
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQqpLEhVMAQy/AtS85RidaKVnJBFnXPyi1NTwOLOWFXDzTDCaoYRDjNQVLvARI2xiprARWMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Audit = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Audit", type text}, {"Status", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Audit"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each #"Name list"),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name"}, {"Name"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"Name", "Audit"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Name", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Name", "Audit"}, #"List of audit performed", {"Name", "Audit"}, "List of audit performed", JoinKind.LeftOuter),
    #"Expanded List of audit performed" = Table.ExpandTableColumn(#"Merged Queries", "List of audit performed", {"Status"}, {"Status"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded List of audit performed",null,"Not done",Replacer.ReplaceValue,{"Status"})
in
    #"Replaced Value"

// Name list
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUVKK1YlWcoIxnGEMFxjDFcZwAzNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Region = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Region", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Name"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

// List of audit performed
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQqpLEhVMAQy/AtS85RidaKVnJBFnXPyi1NTwOLOWFXDzTDCaoYRDjNQVLvARI2xiprARWMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Audit = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Audit", type text}, {"Status", type text}})
in
    #"Changed Type"

// List of suppliers
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUVKK1YlWcoIxnGEMFxjDFcZwAzNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Region = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Region", type text}})
in
    #"Changed Type"

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)