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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Create a new table summarizing data from two sources in Power Query

Hi Guys,

 

I want to get a Master table while combining the data of two different sources in Power Query. The constraints are :

- Data is coming from a sharepoint server and I have absolutly no control over the data shape on either sources

- Unique ID can come from both sources

- The only reliable key between tables is to combine "ID" and "Domain" (from the user point of view)

- All data are usefull / No line can be discarded

 

Here is an example for the 2 source tables and the output I am looking for.

 

Table Source 1 :

 

Key Source 1ID code source 1Domain source 1Specs source 1Lead Lead company source 1Qualification type source 1
ID 1/D1ID 1D1Spec#1; Spec#2;Spec 5.LtdFirst occurence
ID 2/D2ID 2D2Spec#3;Spec#10;Spec#11.GmbhRenewal
ID 3/D3ID 3D3Spec#50;Spec#18.LtdUnder sampling
ID 7/D1ID 7D1Spec#1.SARLRenewal
ID 10/D2ID 10D2Spec#10;Spec#11.SAUnder sampling
ID 11/D4ID 11D4Spec#20;Spec#23;Spec#28.GmbhFirst occurence
ID 15/D4ID 15D4Spec#;Spec#28.LtdUnder sampling

 

Table Source 2 :

Key source 2ID code source 2Domain source 2Sampling level source 2Leac company source 2Impacted companies source 2
ID 2/D1ID 2D1Light.Gmbh.Ltd
ID 4/D3ID 4D3Nominal.SA.Ltd
ID 10/D4ID 10D4Nominal.Ltd.Gmbh;.SA
ID 15/D4ID 15D4Heavy.Ltd.SARL;.SA
ID 17/D2ID 17D2Light.SARL.Gmbh
ID 3/D3ID 3D3Heavy.Ltd.SA
ID 4/D5ID 4D5Heavy.Gmbh.Ltd

 

Master table / Target output :

Key MasterID MasterDomain MasterLead company 1Lead Company 2Impacted companyQualification typeSpecsSampling level
ID 1/D1ID 1D1.Ltd  First occurenceSpec#1; Spec#2;Spec 5 
ID 10/D2ID 10D2.SA  Under samplingSpec#10;Spec#11 
ID 10/D4ID 10D4 .Ltd.Gmbh;.SA  Nominal
ID 11/D4ID 11D4.Gmbh  First occurenceSpec#20;Spec#23;Spec#28 
ID 15/D4ID 15D4.Ltd.Ltd.SARL;.SAUnder samplingSpec#;Spec#28Heavy
ID 17/D2ID 17D2 .SARL.Gmbh  Light
ID 2/D1ID 2D1 .Gmbh.Ltd  Light
ID 2/D2ID 2D2.Gmbh  RenewalSpec#3;Spec#10;Spec#11 
ID 3/D3ID 3D3.Ltd.Ltd.SAUnder samplingSpec#50;Spec#18Heavy
ID 4/D3ID 4D3 .SA.Ltd  Nominal
ID 4/D5ID 4D5 .Gmbh.Ltd  Heavy
ID 7/D1ID 7D1.SARL  RenewalSpec#1 

 

I've tried different topics within the power Bi community, the functions Merge & Append, various relationships modes and creating distinct tables with DAX but each time there is either a loss of data or the KPI are not flexible enough to be exploited properly.

Since there can be several identical values in each columns, lookupvalue is of little help.

 

Does any of you has an idea how to get the proper table in Power Query ?

 

Many thanks,

Regards.

Romuald.

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

Paste my query in Advanced Editor of a new query

Capture7.JPG

query name: Source2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRRMNJ3MVTSAbOAFJjtk5meUQKk9dxzkzJAtE9JilKsDli5ib6LMUS5CUg5iO2Xn5uZl5gDUhjsiKbc0EDfxQSi3tAApMEEVQNIKdQia5BumDZThDZTmDaP1MSySiRNwY5BPiiazPVdjKCazEGajJD9AlIN9xNUhzHcM8Ywz2BaguRzUySfmyIrRgmoWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Key source 2" = _t, #"ID code source 2" = _t, #"Domain source 2" = _t, #"Sampling level source 2" = _t, #"Leac company source 2" = _t, #"Impacted companies source 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key source 2", type text}, {"ID code source 2", type text}, {"Domain source 2", type text}, {"Sampling level source 2", type text}, {"Leac company source 2", type text}, {"Impacted companies source 2", type text}})
in
    #"Changed Type"

Query name :Source1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDNCoMwEIRfJdirqIkGBU+CtBQ8KT2JBxtDK6gVtfT1m6zxJ7SeZljYb2cnz41rjLAdY8MEJwR81nN2wiECJaEURMXcSqZKyLkexgm9GHsPvGPcKEwAETsmM0gKeAC44cxzlMoL1qW9P4WmvOOfslkQrh27M0IKeNihy26wpbh1FR/QWLZ9U3ePheCv3/j6N3Ixi9Lk9yh21uDY0ZLrkbPo8CoWJXoKAS16C4IoBFEtkGD3/UGRmG40qtH2jP8tFF8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Key Source 1" = _t, #"ID code source 1" = _t, #"Domain source 1" = _t, #"Specs source 1" = _t, #"Lead Lead company source 1" = _t, #"Qualification type source 1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key Source 1", type text}, {"ID code source 1", type text}, {"Domain source 1", type text}, {"Specs source 1", type text}, {"Lead Lead company source 1", type text}, {"Qualification type source 1", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Key Source 1"}, Source2, {"Key source 2"}, "Source2", JoinKind.FullOuter),
    #"Expanded Source2" = Table.ExpandTableColumn(#"Merged Queries", "Source2", {"Key source 2", "ID code source 2", "Domain source 2", "Sampling level source 2", "Leac company source 2", "Impacted companies source 2"}, {"Source2.Key source 2", "Source2.ID code source 2", "Source2.Domain source 2", "Source2.Sampling level source 2", "Source2.Leac company source 2", "Source2.Impacted companies source 2"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Source2", "Key Master", each if [Key Source 1] = null then [Source2.Key source 2] else [Key Source 1]),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "ID Master", each if [ID code source 1] = null then [Source2.ID code source 2] else [ID code source 1]),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Domain Master", each if [Domain source 1] = null then [Source2.Domain source 2] else [Domain source 1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column2",{"Key Source 1", "ID code source 1", "Domain source 1", "Source2.Key source 2", "Source2.ID code source 2", "Source2.Domain source 2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Lead Lead company source 1", "Lead company 1"}, {"Source2.Leac company source 2", "Lead company 2"}, {"Qualification type source 1", "Qualification type"}, {"Source2.Impacted companies source 2", "Impacted companies"}, {"Source2.Sampling level source 2", "Sampling level"}, {"Specs source 1", "Specs"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Key Master", "ID Master", "Domain Master", "Lead company 1", "Lead company 2", "Impacted companies", "Qualification type", "Specs", "Sampling level"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Key Master", Order.Ascending}})
in
    #"Sorted Rows"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

Paste my query in Advanced Editor of a new query

Capture7.JPG

query name: Source2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRRMNJ3MVTSAbOAFJjtk5meUQKk9dxzkzJAtE9JilKsDli5ib6LMUS5CUg5iO2Xn5uZl5gDUhjsiKbc0EDfxQSi3tAApMEEVQNIKdQia5BumDZThDZTmDaP1MSySiRNwY5BPiiazPVdjKCazEGajJD9AlIN9xNUhzHcM8Ywz2BaguRzUySfmyIrRgmoWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Key source 2" = _t, #"ID code source 2" = _t, #"Domain source 2" = _t, #"Sampling level source 2" = _t, #"Leac company source 2" = _t, #"Impacted companies source 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key source 2", type text}, {"ID code source 2", type text}, {"Domain source 2", type text}, {"Sampling level source 2", type text}, {"Leac company source 2", type text}, {"Impacted companies source 2", type text}})
in
    #"Changed Type"

Query name :Source1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDNCoMwEIRfJdirqIkGBU+CtBQ8KT2JBxtDK6gVtfT1m6zxJ7SeZljYb2cnz41rjLAdY8MEJwR81nN2wiECJaEURMXcSqZKyLkexgm9GHsPvGPcKEwAETsmM0gKeAC44cxzlMoL1qW9P4WmvOOfslkQrh27M0IKeNihy26wpbh1FR/QWLZ9U3ePheCv3/j6N3Ixi9Lk9yh21uDY0ZLrkbPo8CoWJXoKAS16C4IoBFEtkGD3/UGRmG40qtH2jP8tFF8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Key Source 1" = _t, #"ID code source 1" = _t, #"Domain source 1" = _t, #"Specs source 1" = _t, #"Lead Lead company source 1" = _t, #"Qualification type source 1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key Source 1", type text}, {"ID code source 1", type text}, {"Domain source 1", type text}, {"Specs source 1", type text}, {"Lead Lead company source 1", type text}, {"Qualification type source 1", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Key Source 1"}, Source2, {"Key source 2"}, "Source2", JoinKind.FullOuter),
    #"Expanded Source2" = Table.ExpandTableColumn(#"Merged Queries", "Source2", {"Key source 2", "ID code source 2", "Domain source 2", "Sampling level source 2", "Leac company source 2", "Impacted companies source 2"}, {"Source2.Key source 2", "Source2.ID code source 2", "Source2.Domain source 2", "Source2.Sampling level source 2", "Source2.Leac company source 2", "Source2.Impacted companies source 2"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Source2", "Key Master", each if [Key Source 1] = null then [Source2.Key source 2] else [Key Source 1]),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "ID Master", each if [ID code source 1] = null then [Source2.ID code source 2] else [ID code source 1]),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Domain Master", each if [Domain source 1] = null then [Source2.Domain source 2] else [Domain source 1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column2",{"Key Source 1", "ID code source 1", "Domain source 1", "Source2.Key source 2", "Source2.ID code source 2", "Source2.Domain source 2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Lead Lead company source 1", "Lead company 1"}, {"Source2.Leac company source 2", "Lead company 2"}, {"Qualification type source 1", "Qualification type"}, {"Source2.Impacted companies source 2", "Impacted companies"}, {"Source2.Sampling level source 2", "Sampling level"}, {"Specs source 1", "Specs"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Key Master", "ID Master", "Domain Master", "Lead company 1", "Lead company 2", "Impacted companies", "Qualification type", "Specs", "Sampling level"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Key Master", Order.Ascending}})
in
    #"Sorted Rows"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi @v-juanli-msft / Maggie,

 

Thank you very much for that example, it worked like a charm.

It took me a bit of time to deconstructs the steps into my original queries but up to you, I think I get it. Thank you so much !

 

@artemus I now get what you were saying with the "Merge function", all I needed was more steps to prepare the data and not to perform the Merge as new.

 

Cheers,

Romuald.

 

artemus
Employee
Employee

While you *can* do this, to what end will it be useful? How will you visualize it in Power Bi if you don't know what the column names are?

 

Now if you just want to show the tables in Power Bi, you would need to unpivot the data (unpivot other columns), then do a merge, and use the matrix visual in Power Bi to display the combined data.

Anonymous
Not applicable

Hi @artemus and thanks for your feedback.

 

I do know the column in advance but I cannot change their name  nor the data they display

The point is not to just show the tables in Power Bi but also to establish KPI to help people on their day to day job and others to help clean the data. Hence the constraint where no line can be discarded.

 

I've previously tried the method you are describing and did it again just now but I still have some data loss : only the lines in common from the two sources are shown in the new table.

 

Merge.PNG

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors