Combine Tables from a List of Tables having different columns - Power Query

[Anonymous]

Hi,

I have a List of Tables to Combine together. Each Table contains a main set of columns that are same and another set of columns that are different in each table. I want to combine them so that all the row values fall in the same rows and not on subsequent rows.

List of Tables:

List

Table

Table

Table1:

A	B	C	R1	R2	R3	R4
1	asd	0.6	0.23	0.51	0.33	0.56
2	asd	0.6	ACF	BCA	FGI	LMN
3	asd	0.6	1	1	null	1
1	ghi	0.1	0.11	0.21	0.43	0.96
2	ghi	0.1	DCM	DHJ	TRO	PQR
3	ghi	0.1	1	1	null	1
1	hkl	0.2	0.23	0.51	0.33	0.56
2	hkl	0.2	ARP	VBA	GFI	WQE
3	hkl	0.2	1	1	null	1

Table2:

A	B	C	R5	R6	R7	R8
1	asd	0.6	0.45	0.25	0.11	0.36
2	asd	0.6	DSA	FDG	RER	LUN
3	asd	0.6	1	null	null	1
1	ghi	0.1	0.91	0.43	0.13	0.94
2	ghi	0.1	WED	QYT	SAO	LAP
3	ghi	0.1	1	1	1	null
1	hkl	0.2	0.49	0.78	0.61	0.56
2	hkl	0.2	MAG	ROR	KAL	OLS
3	hkl	0.2	1	null	null	1

Final Table:

Is this possible to do using M-Language when the List of Tables is expanded?

@shaowu459 

https://docs.microsoft.com/en-us/answers/questions/159128/merge-tables-in-a-list-of-tables-having-di... 

[Jimmy801]

Hello @Anonymous 

you can join both tables, and then expand the needed data.

Here an example.

Step Table1 and Table2 are only to simulate your list of tables

let
    Table1 = 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUosTgGSBnpmYNLIGEyZGoIpYyjPTClWJ1rJCE21o7MbkHRydgSSbu6eQNLH1w+s0hhNpSEU55Xm5ICZIEUggfSMTLAiiHWGEMoIQplALLdEWI6s2sXZF0R6eAHJkCB/IBkQGAS3HFklTsszsnMg9hHlc2TVjkEBQDLMCeRzdzeQz8MDXeGWI6vEtDwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, R1 = _t, R2 = _t, R3 = _t, R4 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type text}, {"C", Int64.Type}, {"R1", type text}, {"R2", type text}, {"R3", type text}, {"R4", type text}})
    in
        #"Changed Type",
    Table2 = 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY/BCsIwEET/JecipkmrOQZSPRiNNopI6EEQrFi8iP/vZhfTUDz0DYRHZycExlnBru8bcD6rkbLCKCk4xxA164rAyoltvAauzBrYNi3QnnZoiokZf/P6DMMYHL34fu8f1IVUFFLQG4WSqT+3z40BHi5HoNcu9ut96s/N/MMDfuX9c6C91KowFks6nE6pxvG5vdU428XZG22BzvpUnpt/x3df", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, R5 = _t, R6 = _t, R7 = _t, R8 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type text}, {"C", Int64.Type}, {"R5", type text}, {"R6", type text}, {"R7", type text}, {"R8", type text}})
    in
        #"Changed Type",
    CreateYourList = {Table1, Table2},
    JoinTables = Table.NestedJoin
    (
        CreateYourList{0},
        {"A","B","C"},
        CreateYourList{1},
        {"A","B","C"},
        "Table2"
    ),
    #"Expanded Table2" = Table.ExpandTableColumn(JoinTables, "Table2", {"R5", "R6", "R7", "R8"}, {"R5", "R6", "R7", "R8"})
in 
    #"Expanded Table2"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[mahoneypat]

You can create your desired table with a Merge step.  On your first query, click on Merge Queries, choose your second one, and in the join screen popup choose the first three columns in order in both tables.  Then expand the column with the merged tables.

Regards,

Pat

[Anonymous]

@mahoneypat it is a list containing multiple tables that i need to combine, but the rows are not getting merged, but falling in separate rows.

[shaowu459]

Change Source to the previous step name, there are TWO SOURCE in my code.

= Table.PromoteHeaders(Table.FromColumns(List.FirstN(Table.ToColumns(Table.DemoteHeaders(Source{0})),3)&List.Combine(List.Transform(Source,each List.Skip(Table.ToColumns(Table.DemoteHeaders(_)),3)))))

[Anonymous]

I see your solution is dynamic to incorporate multiple number of tables. However, i am getting the following error when i tried to implement your code :

Expression.Error: We cannot convert a value of type Record to type Table.
Details:
    Value=Record
    Type=Type

 My first 6 columns are similar in my original file:  

Code:

= Table.PromoteHeaders(
                    Table.FromColumns(
                         List.FirstN( Table.ToColumns( Table.DemoteHeaders( RemovedCol1Column{0} ) ) , 6) & 
                         List.Combine( List.Transform( RemovedCol1Column, each List.Skip( Table.ToColumns( Table.DemoteHeaders(_) ) , 6) ) )
                    )
               )

`RemovedCol1Column` is the previous step containing the List of tables.

[shaowu459 · Accepted solution]

= Table.PromoteHeaders(
                    Table.FromColumns(
                         List.FirstN( Table.ToColumns( Table.DemoteHeaders( RemovedCol1Column[List]{0} ) ) , 6) & 
                         List.Combine( List.Transform( RemovedCol1Column[List], each List.Skip( Table.ToColumns( Table.DemoteHeaders(_) ) , 6) ) )
                    )
               )

add [List] after RemovedCol1Column, List is the first column name of table in RemovedCol1Column.

[Anonymous]

Man, you are a Genius! 😀

[shaowu459]

are there more thant 2 tables in the list and the first 3 columns are all the same for all the tables?

[Anonymous]

Yes, multipe tables depending on the number of csv files that get converted to a list of tables. Yes, the initial 3 column rows (multiples of 3 rows) may be same across the different tables. If same, then their rows should get merged on the same rows, as their R1...Rx columns will be different. 

[mahoneypat]

Got it.  How many tables are in the list?  If just the two shown, you have at least two options

1. Convert list to table, transpose it, then add a column with the Table.Join of the first two columns, specifying the columns in order

2. Insert a step after, use Table.Join and reference the two tables with #"Previous Step"{0} and #"Previous Step"{1}

Replace with your actual previous step name.

Regards,

Pat

[Anonymous]

More than 2 tables. These could vary depending on number of csv files that get converted to a list of tables.