Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
amitchandak
Super User
Super User

@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

View solution in original post

5 REPLIES 5
Fowmy
Super User
Super User

@Anonymous 

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! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

FrankAT
Community Champion
Community Champion

Hi @Anonymous 

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)

amitchandak
Super User
Super User

@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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hello Greg,

 

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

 

Thanks.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.