Can someone please make a tutorial, I am a bit gre...

Figaro67 · ‎06-03-2018

Every morning I download an excel sheet with ticket information. Tickets like in questions or problems. This sheet has columns like ID, Title, Group and so forth. I have been pulling out this sheet since 2016.

This sheet is now 32000 rows. This has been working fine. I have been opening the file to PowerBI and making my graphs and stuff.

Due to the GDPR the Column "Title" was completely removed from this sheet, leaving me with a big problem, because I really need the Title column.

I found out that the Column was removed the 15th of May. Luckily I found the file in one of my folders until the 15th of May with the Title column still in it. I also found another portal where to find the Titles from the 15th until today.

To make the story short I have the following files.

Sheet 1: File from 2016 until today without the Title column. (This file contains all the ticket ids.)

Sheet 2: File from 2016 until the 15th of May with the Tille column. (This file contains only the tickets ids until the 15th May)

Sheet 3: File from the 15th of May until today, with only two columns ID and Title (this file contains the ticket ids from the 15th of May until today)

How do I combine the three files? Can you show me in a tutorial like situation? I am still green in this.

MCuster · ‎06-03-2018

Try this one...

Figaro67 · ‎06-03-2018

Thanks I am watching it but this is not going to solve my problem. I wonder if I can pay someone to help me.

Figaro67 · ‎06-03-2018

I get this error when I try to combine files.

Anonymous · ‎06-03-2018

Hi @Figaro67,

I think I tried to reply to this post but it seems to have disappeared. I'll assume you have 3 Excel files. The steps are as follows

Import all 3 tables into Power BI
In the query editor - append Sheet 2 and Sheet 3 as a new query. Keep only the Ticket ID and Title columns in this append query.
Create a new merge query to merge Sheet 1 and the new append query you created above. Merge the query on the Ticket ID column. You can leave the Join Type as Left Outer (this will keep all entries in Sheet 1 regardless of if it finds a match from the created append query).
Finally right click all queries (except the final table above) and uncheck the box Enable Load. This will ensure only the full table is loaded to the model.

Hope the steps help. Let me know if you have any questions.

Regards,

Kris

Figaro67 · ‎06-03-2018

I have done this.

Opened Power BI desktop

Clicked on Get Data and chosen the three excel sheets one after the other.

I go to the Query editor and you say that i have to append sheet 2 and sheet 3 as a new query. How do I know what is Sheet one and Sheet 2. I will pick All Ids and Before 15th.

I just lost it again... Can I call you tomorrow?

Anonymous · ‎06-03-2018

Great. So the queries are in. You can append before 15th and after 15th as a new append query in the query editor. Call this query something like beforeandafter15th. Then merge the newly created beforeandafter15th with your All IDs query as stated in my first response. This should create the desired table.

Unfortunately, I’m not available for calls.

Best,
Kris

Figaro67 · ‎06-03-2018

Does it matter that the tables are not identical in their form?

The sheet "before15" contains the column "subject" and four other columns which do not exist in the sheet called "all IDs" file.

The "after15" sheet contains only five columns. that exist in the "before15" but not in the "all IDs" file. The only Column that is present all the files is the one called "TicketID"

Anonymous · ‎06-03-2018

No this should not matter. What will happen when you merge the append query beforeandafter15th with your All IDs query wherever there is a match it will add all the columns from the append query to the rows of the All IDs query wherever it finds a match on Ticket ID. Once you have done the merge query you can click the circled area in the image below to expand out the merged table rows. When you expand you can also choose which columns to keep with the check boxes.

Anonymous · ‎06-03-2018

Hi @Figaro67,

Are the files you are trying to import Excel extracts? I would assume so as you mention Sheet 1, 2 and 3. This situation can be solved in the query editor. The steps are as follows

Import Sheet 1, 2 and 3 by going to Get Data > Excel. If you have 3 seperate files it might help to do them one at a time in this instance, rather than the entire folder.
In the query editor append queries as new for Sheet 2 and Sheet 3. In this query you need only to keep the Ticket ID and title. Lets call this Append23 for now.
Merge queries as new for Sheet 1 and Append23. When merging the queries you will be presented with a window. Select Sheet 1 on the top dropdown and Append23 on the bottom dropdown. Click the Ticket ID columns on both tables that show to state that you wish to merge on this column. For join kind a left outer should be fine.
You should then be left with a query like below. If you click the box circled red you can expand the view. When the pane shows up just leave the Title box checked. You should then have a query with your full table along with the titles.
Finally right click all other queries and uncheck the "Enable Load" box. This will ensure only the full table is loaded to the model.

These steps should solve your issue. Let me know if you have any questions about the above.

Regards,

Kris