cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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:

 

PBi.JPG

 Thanks and best regards.

1 ACCEPTED SOLUTION
Super User IV
Super User IV

@MarwenA , 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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

@MarwenA 

Duplicate the Supplier Table and do the transformation:

You can download the file:HERE

 

Fowmy_1-1600178909277.png

________________________

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Super User II
Super User II

Hi @MarwenA 

you can do it in Power Query like this:

 

15-09-_2020_15-42-40.png

 

// 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)

Super User IV
Super User IV

@MarwenA , 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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hello Greg,

 

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

 

Thanks.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors