date spreadsheet course

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Power BI training and help » Date spreadsheet from course

Date spreadsheet from course

resolvedResolved · Low Priority · Version 365

Craig has attended:
Power BI Reporting course

Date spreadsheet from course

I recently attended a Power BI course run by Jens where we used a date spreadsheet. I thought I had e-mailed it to myself but I have not received it. Would it be possible to send a copy of this spreadsheet to my e-mail address, craig.murphy@secalliance.com.
Many thanks
Craig Murphy

RE: Date spreadsheet from course

Hi Craig,


I have sent you the file.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Date spreadsheet from course

Thank you Jens
How can I expand these dates out to the end of 2024. If I drag them down it appears to start from the year 1900 rather than 2021.
Craig

RE: Date spreadsheet from course

Hi Craig,

Please select the entire last row from A4750 to N4750.

Place your mouse cursor on top up the handle bottom right in cell N4750 and when you cursor display a black plus, click and drag down as far as you need

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Date spreadsheet from course

Thanks Jens

RE: Date spreadsheet from course

In referring back to the example from our course, am I correct in thinking that the only duplicating factor in an order number is the Product ID?

If so, what would I do if there were other duplicating factors, like if there were more than one rep involved in an order, and if there happened to be more than one customer in an order.

Would this require additional sheets like the 'Line Items' tab?

Many thanks
Craig

RE: Date spreadsheet from course

Hi Craig,

If you have orders where more than one rep is involved you will need a transaction table. In the transaction table you should have a column with the order number to use as the foreign key which you will need to relate to the order number in the orders table (the primary key). Then in the transaction table you will need a record for each rep under each order number.

If have an order 1000 and 3 reps has been involved in this order you will need 3 records in the transaction table with the same order number 1000. One for each rep with the details you need to have.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Date spreadsheet from course

So I would need to ensure three rows in the table all with order number 1000 and change the name of the rep in each of those rows?

What if there are also 2 customers on the same order?

Would I then need 6 records for the same order?

Edited on Thu 21 Dec 2023, 08:34

RE: Date spreadsheet from course

Hi Craig,

Yes this is correct
"So I would need to ensure three rows in the table all with order number 1000 and change the name of the rep in each of those rows?"

And the same if you have many customers per order. You will need a transaction table with one row for each customer and again if the order number is 1000 and you have two customers on the same order you will need two rows with the order number 1000 and change the name of the customer in each of those rows. You will then need to make a relationship between from the column where you have the order number in the transaction to the column where you have the order number in the orders table.

If you Google database normalization or normalized tables you can get the knowledge you need to understand data modelling, which are very important to get the data model correct in Power BI.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Date spreadsheet from course

OK thanks Jens.

What I have done is create 3 sheets, one with multiple rows for product, the next with multiple rows for Rep and the other with multiple rows for customer.

Ensuring the order number remains consistent across all sheets should this still work?

RE: Date spreadsheet from course


Hi Craig,

I do not know your data set but in a data model you will need need a combination of DIM (lookup tables) and FACT (transaction tables).

I assume that you have a DIM table where you have a row for each customer identified by a customer id, a DIM table for each product identified by a unique product id, and a DIM table for your orders where each order is identified by a unique order number.

Then you need a number of transaction (FACT tables) tables. A FACT table for each transaction you have for each order, product, and customer.

I am sorry but I cannot tell you exactly how you should build your model. You may or may not have to work with more tables.

I assume you will need another FACT table if you have a product price in the product table. You will need a transaction if you change the prices.

I am sorry I am on my way to the airport going on my Christmas holiday. We are happy to help you if you have more questions. If you have more please raise it as a new question. Another trainer will then help you, and if you reference our conversation, the trainer will have a look at this.



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Date spreadsheet from course

Thank you Jens. Have a great Christmas. This is not an urgent enquiry, I am just playing around with my dataset. If I do have more questions I will be in touch with your colleagues.

 

Training courses

 

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Power BI tip:

Scheduled Refresh and Power BI Service

If you're using Power BI Service, set up scheduled refresh for your datasets. This ensures that your reports are always up-to-date with the latest data. Understand the refresh limits imposed by Power BI Service and optimize your data model and queries to stay within those constraints.

View all Power BI hints and tips


Server loaded in 0.07 secs.