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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
IanR
Helper III
Helper III

Create new table with subset of columns from several other tables

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

1 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.

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

You probably want a Merge table.



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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
Microsoft Employee
Microsoft Employee


@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"

append.gif

 

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

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?

 

PQ Insert table with different column names.png

 

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.

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?

 

PQ Insert table with different column names.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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