Create new table with subset of columns from several other tables

[IanR]

Hi,

I am working in CRM and would like to create a timeline table which will effectively be the fact table for my model. I'd like to populate it with the date, contact reference, campaign reference and activity type from several other tables (two email marketing tables, two web activitiy tables and a leads table). The 'source' tables have all got other rows that I do not want included in the timeline table. The Append function in Power Query seems to demand that the tables appended from and to both have the same number of columns and creates unwanted new colimns if this is not the case. It also deamnds that correspomnding columns are in the same order in each table. This isn't what I want.

I'd like to be able to select the columns that I'd like to append to the new table, select which columns thay append to and not worry about additional columns that I don't want to append. is there a way of doing this?

I thought I could start this by copying my largest table (one of the email tables; 1.6 million rows) and removing the columns that I don't want. Unfortunately, when I copied the query for this table it didn't copy accross the data it copied accross all the steps that I had taken to import and transform it. This is a historical table. I have turned off it's 'Include in Report Refresh' because it takes 90 minutes to refresh. I don't want to have to import it all again. I can see the logic in why copy works this way but in this case I suspect that I will need to start by appending to a new table. If I can select the columns.

Thanks

[Greg_Deckler]

You probably want a Merge table.

[IanR]

You'll have to excuse my inexperience but insn't a merge for adding two tables toghether and not changing the number of rows? I want the various tables to be added together by increasing he number of rows. I would like to add a table of five web visits to a table of ten email opens and end up with a resultant table of fifteen activities.  

[Eric_Zhang]

---

@IanR wrote:

You'll have to excuse my inexperience but insn't a merge for adding two tables toghether and not changing the number of rows? I want the various tables to be added together by increasing he number of rows. I would like to add a table of five web visits to a table of ten email opens and end up with a resultant table of fifteen activities.  

---

@IanR

May I know why you have to append those two email marketing tables, two web activitiy tables and a leads table? From the table names, I can see those tables contains different data, the appended table would contain messy data so that you probably are not able to present what you expect.

Though, techinically, it can be done through Power Query.

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUoC4mQgTlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Table1 ,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),

    Table2= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlXSUUoD4nQgzgDiTKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Changed Type_" = Table.TransformColumnTypes(Table2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
 

    SelectColumnFromTable1 = Table.SelectColumns(#"Changed Type",{"Column1","Column2"}),

    SelectColumnFromTable2 = Table.SelectColumns(#"Changed Type_",{"Column1","Column2"}),
    #"Appended Query" = Table.Combine({SelectColumnFromTable1 , SelectColumnFromTable2 })

in
    #"Appended Query"

[IanR]

Hi Eric_Zang,

I think that is the solution but I never dabbled in M before so it will take me a while to digest. I'll try it this weekend - sorry that this will introduce a delay before I confirm. I assume that the first two chunks (Table1 = ... and Table2 = ... are setting up some test data and that what I need are the Table.SelectColumn... lines and the Table.Combine lines.

Great animation/video. How did you do that?

To answer your 'why am I doing this' question: I am taking data from several different tables including email open and click data from two different bulk email systems and web visit data from two different sources (also store visit records and one or two others). Conceptually these are all the same thing, theya are activities that happen to a contact. I am therefore taking the essence of each activity (date, contact, campaign) and puting these into one Activities table so that I can count and assess all the interactions that we have with a client up to the point that we make a sale. I am hoping that this will allow me to create a much simpler relationship diagram and make more use of the relationships in DAX. At the moment I have a tangled web of relationships and often can't create new ones because they would lead to 'ambiguity'. 

Thanks

[IanR]

Hi Eric_Zang,

Using your solution it looks as though the column names have to be the same in the two tables, which isn't the case with my data. I'd like to be able to do something like 'append data in columns 1, 5 and 10 from table1 into columns 1, 2 and 3 in table2'. I tried changing one of the lines in your code:

From
   = Table.SelectColumns(#"Changed Type",{"Column1","Column2"})
To
   = Table.SelectColumns(#"Changed Type",{"Column2","Column3"})

Which gave the result below with null values where the names didn't match.

Is there a way to do this where the column names do not have to match?

[parry2k]

@IanR can you rename the table columns before append and then it will append under the same column

[IanR · Accepted solution]

The columns I am appending are already involved in a large number of measures and I assumed that simply changing their names would break these measures. As a workaround I have created duplicates of the columns required, changed their names and used the new columns in appends created using the GUI. It sort of works but it has bloated my model and I need to tidy the process up.

[IanR]

Hi Eric_Zang,

Using your solution it looks as though the column names have to be the same in the two tables, which isn't the case with my data. I'd like to be able to do something like 'append data in columns 1, 5 and 10 from table1 into columns 1, 2 and 3 in table2'. I tried changing one of the lines in your code:

From
   = Table.SelectColumns(#"Changed Type",{"Column1","Column2"})
To
   = Table.SelectColumns(#"Changed Type",{"Column2","Column3"})

Which gave the result below with null values where the names didn't match.

Is there a way to do this where the column names do not have to match?