Combining 2 tables into one

[Anonymous]

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.

[Fowmy]

@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

[FrankAT]

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)

[amitchandak · Accepted solution]

@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

[Greg_Deckler]

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

[Anonymous]

Hello Greg,

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

Thanks.