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
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
Solved! Go to 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.
You probably want a Merge table.
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 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.
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"
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?
@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?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |