Large dataset split into two tables, how to recombine columns for visuals? Or is there a better way?

[Anonymous]

Hello,

If I understand it right, I have created 1 to 1 bi directional relationship between tables, but they are not showing all of the data when I use them in the visuals. I thought this would solve my problem but I have found myself back in the query editor now.

The two tables have different numbers of columns (743, 692) and I only need the data from maybe ~50 identical columns for now. When I try to grab them all with one query, I get a time-out error. (the data is retrieved, a ton of records are unpacked, then all columns with only null values are deleted. )

Everything is running very slow, and I can't successfully append or merge anything. 

I am hoping to  append multiple columns from multiple tables and give myself one table to work off of.

Borrowing an example I found online, 

If my data looks something like below:

Table A

goalie	forward
Di Stefano
Di Stefano	Gento
Di Stefano	Rial
Mateos	Di Stefano

Table B:

Goalie	forward
Marquitos	Rial
Gento

Now, I need my data to look like below: 

Table C:

goalie	forward
di stefano
di stefano	Gento
di stefano	Rial
Mateos,	De  Stefano
Marquitos	rial
Gento

I'd really love some ideas if there is a better way, or if not then to know if this selective combination is possible.

The post I modified to fit mine used unpivoting to combine rows from two columns in the same table, but I don't see a way to unpivot multiple table's columns at once.

Thanks for reading!

[Greg_Deckler]

@Anonymous What data source are you using? You can unpivot multiple columns by using "Unpivot Other Columns" or by selecting all the columns you want using Ctrl/Shift, etc. and then Unpivot columns.

[Anonymous]

Thanks for the quick reply!

My data source is a custom api built on top of MongoDB using find() queries. 

It pulls from multiple databases and aggregates it into a huge Json format. I do not have access beyond this API.

I'm unsure about the unpivoting thing you mentioned, when I hold CTRL and click back to the other querie the column I had selected is no longer selected. 

I would think there would be a DAX formula to append only specific columns to a new table, which would solve my issue, but I'm not seeing it in the docs.

[Greg_Deckler]

@Anonymous OK, we may not be talking the same language. When you unpivot columns you do that in the same table, not multiple tables. If you want to "append" columns, you would use a merge query to add columns from one table to another table. What your example shows however is an append operation, an Append query. Soooooo...I'm totally confused in terms of what you are trying to accomplish.

[Anonymous]

@Greg_Deckler 

Sorry about the mixup, I think mentioning unpivoting was a mistake.

Let me try again:

I want to append the info from the two tables together from just the 50 relevant columns (which have identical headers in each table), into one smaller updated table (that has all of the rows from both Table A and Table B combined in each of the columns)

Or, I guess, find out if there is a better practice where I can leave my data divided into two queries and still capture the full data set when creating visuals.

Once again I really appreciat the help!

[Greg_Deckler]

@Anonymous Seems like what you want to do is to start with your two queries and filter them down to the relevant 50 columns. Probably best to do that in Advanced Editor since it sounds like you have a ton of columns and then you can also just copy and paste the step between queries. Right-click on the queries and disable load. Create a new Append query that appends those two queries.

[Anonymous]

@Greg_Deckler Ok that does sound like  the right approach, can you help me with formatting the merge? 

Table.Combine({

     Table1.FromColumns("a column name),

     Table2.FromColumns("a column name)})

and it was unsuccessful.

[Greg_Deckler]

@Anonymous Should be: Table.Combine({Query1, Query1})

[Anonymous]

@Greg_Deckler 

Right, but how to select a specific list of columns within that? Trying just to combine the queries does not work.

Something like:

NewTable = Table.Combine({

                              Table.SelectColumns(Table A, "Goalie", "Forward"

                              Table.SelectColumns(Table B, "Goalie", "Forward" })

[Greg_Deckler · Accepted solution]

@Anonymous I would just remove the columns in the queries but you can do it like this:

let
    Source = Table.Combine({ Table.SelectColumns(#"Table A",{ "Goalie", "Forward"}), Table.SelectColumns(#"Table B",{ "Goalie", "Forward"})})
in
    Source