PL-300

Box 1: Dual -
Customer should use the dual storage mode.
Dual: Tables with this setting can act as either cached or not cached, depending on the context of the query that's submitted to the Power BI dataset. In some cases, you fulfill queries from cached data. In other cases, you fulfill queries by executing an on-demand query to the data source.
Note: You set the Storage mode property to one of these three values: Import, DirectQuery, and Dual.

Box 2: Dual -
You can set the dimension tables (Customer, Geography, and Date) to Dual to reduce the number of limited relationships in the dataset, and improve performance.

Box 3: DirectQuery -
Sales should use the DirectQuery storage mode.
DirectQuery: Tables with this setting aren't cached. Queries that you submit to the Power BI datasetג€"for example, DAX queriesג€"and that return data from
DirectQuery tables can be fulfilled only by executing on-demand queries to the data source. Queries that you submit to the data source use the query language for that data source, for example, SQL.

Box 4: Import -
Import: Imported tables with this setting are cached. Queries submitted to the Power BI dataset that return data from Import tables can be fulfilled only from cached data.
Reference:
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-storage-mode

I mean for Both Date and Customer dimension tables

thanks for sharing your comment so I would ask if in case the Sales and the Sales Aggregate fact tables are both in near real-time will be a Direct Query not dual? thanks in advance

Correct. IF someone still unable to understand I would highly recommend going through this link. Excellent explanation

Technically Yes, Correct 

Dual (Composite) Mode:
The dual storage mode is between Import and DirectQuery. it is a hybrid approach, Like importing data, the dual storage mode caches the data in the table. However, it leaves it up to Power BI to determine the best way to query the table depending on the query context.

1) Sales Must be Refreshed in Near real time so "Direct Query"
2) Sales Aggregate is once per week so "Import" (performance also required)
3) Both Date and Customer has relationship with both Sales and SalesAggregate tables so "Dual"
because to support performance for DirectQuery(Sales) and Import(SalesAggregate)

My interpretation of the answer:

there are 2 fact tables connected to 2 dim tables. Sales fact table needs DirectQuery.. Sales Aggregate table needs to weekly refreshes hence Import mode. But both dimension tables have to serve these fact tables and hence they will be in DUAL MODE. Am I correct?

Customer - Dual
Date- Dual
Sales - Directquery
Salesaggregate - Dual

Import - Customer is cached
Import - Date is cached
Direct Query - Sales is not cached
Dual - Sales Aggregation either cached or not cached

If the tables are from the same database in this model. Then there is only one frequency of refresh possible. It cannot be weekly , since it says "Customer must be refreshed daily." 
Also Import mode generally minimizes the load time of visuals compared to Dual mode. This is because Import mode caches all the data in-memory, allowing for very fast query performance and quick rendering of visuals

Customer: Import - refresh daily requirement
Date: Dual - just because Sales is direct query and might be more efficient to have as Dual.
Sales: Direct Query - Real Time by requirements
Sales Aggregate: Imported, is weekly, but there is no saving in refresh, since the refresh is daily because the Customers table.

I dont agree, sales are to be done near real time so direct query is right. Sales aggregates once a week, then dual is ok as we want performance too. Customer is dual too. while Date, updated once every 3 yrs can be import.

In PBI there are 3 connection modes:
1 - Import Mode, all the data is loaded into the model once and you need to manually refresh data before get latest updates
2 - Direct query mode (or similar live for Azure Analysis Service or SSAS), in which PBI sends queries to the sources each time a visualization is refreshed or interacted with
3 - Dual, it is an hybrid mode

Saying that, we need for sure a direct query for the sales (since it is near real time) then the sales aggregate should be put to Import since there is an once per week update. Finally, as Microsoft suggest, the dimension tables should be set to dual (date and customers in this case)

Why does Sales Aggregate use Import as per the solution? Where as Date uses Dual.
In both the cases there is no real time refresh required, why are we going with Import mode for sales aggregate??

BTW, I understand logic behind selecting Dual for Date as it is dimension table and we intend to reduce number of limited relationships.

Sales need to be real time so a DirectQueary storage mode.
Sales aggregate as is a weekly load you can use Import Mode and save on performance.

As you have one of each, the other table connected to this source should be Dual. If they where not connected they would be Import mode.

Give answer is correct. The tables that are connected to both Direct Query and Import should be set as Dual.

Customer(dual), date(dual), sales(direct query) , salesaggregate(import)

1. Customer table: Import mode should be used because it needs to be refreshed daily, and importing the data will provide better performance for visuals.
2. Date table: Import mode should be used because even though it is refreshed once every three years, importing it will not significantly affect performance, and it ensures that visual loads quickly.
 3. For Sales table: Dual mode should be used for this table because it needs to be refreshed in near real-time.
4. SalesAggregate Tabe: Import mode should be used because it needs to be refreshed once per week, and importing the data will insure better visual performance.

3. Date table: Import mode should be used because even though it is refreshed once every three years, importing it will not significantly affect performance, and it ensures that visual loads quickly.

This link is helpful in describing when to use which import mode. The requirement of the data (real-time refresh vs. performance) and the relationships in the exhibit are what this question comes down to.
https://learn.microsoft.com/en-us/training/modules/choose-power-bi-model-framework/1-introduction

I found the perfect explanation why the answer is correct:

Hi everybodyI Does anyone could explain me why 1 and 2 must be Dual? Could be Import or no way?

The customer table should be stored in Direct Query mode as the table has a solid line on the top. The solid line means Direct query, and a dashed bar at the top of the table means Dual.
So I think the answer is Direct Query, Dual, Dual, Import.

Is this correct?: THe tables should be set to DUAL since it will interact with a DQ table (sales) for query optimization reasons

i think the given answer is Right. please check the link and confirm back for the rest of the people.

Customer: Dual
Date: Dual
Sales: DirectQuery
SalesAggregate: Import

@Elena3061 I guess you should dual instead of DirectQuery because of their relationship with both import mode table and directQuery mode table. Take a look at _Jay_'s post and at the "Limited Relationship" paragraph in this article https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand

Can someone explain for me why should we use Dual instead of DirectQuiry for Date and Customer tables? Thank you

HOTSPOT -
You plan to create the Power BI model shown in the exhibit. (Click the Exhibit tab.)
//IMG//

The data has the following refresh requirements:
✑ Customer must be refreshed daily.
✑ Date must be refreshed once every three years.
✑ Sales must be refreshed in near real time.
✑ SalesAggregate must be refreshed once per week.
You need to select the storage modes for the tables. The solution must meet the following requirements:
✑ Minimize the load times of visuals.
✑ Ensure that the data is loaded to the model based on the refresh requirements.
Which storage mode should you select for each table? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
//IMG//

If you choose to split it will create 2 columns but extract will give 1 column.

Selected Answer: C
Answer C is best approach
Split the Logged column by using "at" as the delimiter.

You actually can do that if you click on the "Select or enter delimiter" in the "Split Column by Delimiter" window that pops up after you click on "Split Column" in the "Transform" tab on top of your Power BI window. After you select the --Custom-- option from the drop down menu in the "Select or enter delimiter" drop down list, you can write "at" in the text box that appears below the drop down list.

delimiter uses only one character, so "at" is not valid

C,

You should split the Logged column by using "at" as the delimiter. This will allow you to separate the date and time into separate columns, which will enable you to analyze the complaints by date and use a built-in date hierarchy. Alternatively, you could also use a transformation to extract the date and time from the Logged column and set the data type of the new columns to Date and Time, respectively. Option A is incorrect because it only extracts the last 11 characters of the Logged column, which would not include the date. Option B is incorrect because the data in the Logged column is in a non-standard date format and cannot be directly converted to the Date data type. Option D is incorrect because it only extracts the first 11 characters of the Logged column, which would not include the time.

Selected Answer: D
To enable Power BI’s built-in date hierarchy, the column must be recognized as a valid Date type. The time and the extra text (“at”) prevent Power BI from interpreting it correctly as a date.
By extracting the first 11 characters, you get just:
2018-12-31

It is not mentioned that time is need.

Selected Answer: C
Splitting the Logged column by the delimiter "at" separates the date and time into different columns, allowing you to convert the date part into a proper Date type and use Power BI’s built-in date hierarchy for analysis.

Selected Answer: D
1. The question didnt asked for time, it asked for date only. 
2. When using a delimited, there are two spaces before and after "at".
3. C result still requires cleanup as it will give two columns one of them is the time.

So the answer is D for sure.

Selected Answer: D
Both answers C and D are correct, both get the job done since the question doesnt specify you need to keep the time. The time is meaningless, so keeping it is unnecesary. I guess i understand why people would like both columns, but it's better to just get rid of unnecesary columns to improve the model

Selected Answer: D
The Correct Answer is D for two main reasons
1) The question is only interested in creating a DATE hierarchy, so time is irrelevant here, extracting the first 11 characters covers the entire Date.
2) the 'at' delimiter of option C will include the spaces as suffix and prefix, in the new split columns

Selected Answer: C
Split the logged column with the delimiter space

Selected Answer: D
D. Apply a transformation to extract the first 11 characters of the Logged column.

Extracting the first 11 characters will give you 2018-12-31, which is the date part. You can then set the data type of this new column to Date. This is the most straightforward and efficient approach.

Selected Answer: D
Explanation:
The Logged column format is "2018-12-31 at 08:59".
To analyze by date and use a built-in date hierarchy, you need to extract just the date part.
The first 11 characters ("2018-12-31") represent the date.
Once extracted, you can convert this new column into a Date data type for proper analysis and use of the date hierarchy.
This ensures that the data is in a suitable format for date-based analysis.

Selected Answer: C
Answer is correct. Tried and tested.

Selected Answer: B
I have seen the same question in the Microsoft pl-300 preparation course on coursera and here is the answer to that question:

You have a CSV file that contains user complaints. The table contains a column called Logged, which has data and the time of each complaint. The data is logged in the following format: 2022-12-21 at 08:35. You want to analyze the complaints by the logged date and utilize Power BI’s built-in date hierarchy. What should you do?

- Split the logged column using ‘at’ as a delimiter.
- You can use the same column as the date hierarchy.
- Apply transformation to extract the column's first characters that only contain the date.
- Change the data type of the logged column to Date. ✔

Selected Answer: D
I have seen the same question in the Microsoft pl-300 preparation course on coursera and here is the answer to that question:

You have a CSV file that contains user complaints. The table contains a column called Logged, which has data and the time of each complaint. The data is logged in the following format: 2022-12-21 at 08:35. You want to analyze the complaints by the logged date and utilize Power BI’s built-in date hierarchy. What should you do?

1- Split the logged column using ‘at’ as a delimiter.
2- You can use the same column as the date hierarchy.
3- Apply transformation to extract the column's first characters that only contain the date.
4- Change the data type of the logged column to Date. ✔

Selected Answer: D
My bad, A is talking about the last 11 characters, so my choice is D.

Selected Answer: A
And yeah you should change the data type after D so the correct answer should be A

You have a CSV file that contains user complaints. The file contains a column named Logged. Logged contains the date and time each complaint occurred. The data in Logged is in the following format: 2018-12-31 at 08:59.
You need to be able to analyze the complaints by the logged date and use a built-in date hierarchy.
What should you do?

and why not Excel Workbook?: I think that this connector allows you to connect directly to an Excel file stored in OneDrive for Business. While some may argue that it could create a "local copy," this connector actually connects to the file in the cloud and allows you to refresh the dataset (with scheduled refreshes) in PowerBI.com without the need for a gateway.

A, D and E are valid answers, which two are tem more accurate?

It would depend if there is authentication involved with the SharePoint folder or the Web address, what if they are using basic auth? Anyway, either of the answers, a,b,c,d all offer difficulty for me to answer as the correct one. So, I guess it is which is the most correct?

but OneDrive is cloud and we do not need a gateway.

I tried both and they work perfectly, and of course, you need the path (in this case the URL of the excel file on One Drive) of the file, so I don´t see the problem you say...

Works just fine, this is how you do it :)

Try it. D and E won't work. Its looking for a URL

Selected Answer: DE
We can import an excel file from multiple connectors (excel workbook, folder, web, sharepoint) but if we must refresh the data from the service with no gateways then We must use web and sharepoint connectors

Selected Answer: DE
A, B, C: wrong! Would work technically, but the connection will be only to the local copy of the file, no refresh from the online version stored on OneDrive
D: correct, but more complicated than option E
E: correct, this is the best option to import from OneDrive

Selected Answer: DE
D& E should be the answer

Selected Answer: DE
D. SharePoint Folder
OneDrive for Business is built on SharePoint Online.

Using the SharePoint Folder connector allows Power BI to access all files in a document library.

You can then filter by file name or folder path to isolate your Excel file.

This method supports scheduled refresh in the Power BI Service.

E. Web
You can use the Web connector with a direct link to the Excel file stored in OneDrive.

Make sure to clean the URL (remove ?web=1) and authenticate using Organizational Account.

This also supports refresh in powerbi.com, as long as you use OAuth2 authentication3.

Selected Answer: DE
DE is the right answer

Selected Answer: AB
Excel/Workbook, Text/CSV

Selected Answer: DE
For people who are using chat GPT, if you ask another question: which connectors allow refresh from powerbi.com, it will show you that if the file is on OneDrive, we should connect it via SharePoint, not a direct support. It answered A because it is a possibility and GPT does not always consider all the logic.

Selected Answer: AE
It doesn’t say Sharepoint folder. It explicitly says OneDrive.

Selected Answer: AD
Correct Answers:
A. Excel Workbook: This is a direct and commonly used connector for importing Excel files into Power BI. It supports OneDrive integration and allows scheduled refreshes on powerbi.com.
D. SharePoint Folder: If the file is stored in a OneDrive folder, which is backed by SharePoint in the backend, the "SharePoint Folder" connector can be used to access it. This method is also refreshable.
Explanation of Other Options:
B. Text/CSV: Not applicable for Excel files unless the file is exported as a .csv. Even then, it won't support refresh like the Excel connector.
C. Folder: Used for combining files in a folder but not specifically for accessing a single Excel file in OneDrive.
E. Web: Can be used if the file is accessible via a web URL (e.g., a public link), but this is not optimal or commonly used for OneDrive-stored files.

Selected Answer: AD
Since the Excel file is stored in a OneDrive folder, you need a connector that allows cloud-based access and scheduled refresh in Power BI Service.

✅ A. Excel Workbook (Correct)
This is the most direct and recommended way to connect to an Excel file stored in OneDrive.
It allows scheduled refresh in Power BI Service.
✅ D. SharePoint Folder (Correct)
OneDrive for Business is built on SharePoint, so the SharePoint Folder connector can also be used.
This is useful if you need to access multiple files in a OneDrive folder at once.
It supports scheduled refresh in Power BI Service.

Selected Answer: AE
I tested this and answer is confirmed A and E. Question is what connectors, Excel workbook is a clear choice as stated on the question "Microsoft Excel file in a Microsoft OneDrive folder" when you select the 'excel workbook' connector, it grabs the file from one Drive. And E is the other clear answer (Web) connector, on the excel file , go to file>Info>copy path. Use the path and paste it on the URL section and I was able to retrieve and do a refresh. Nothing on the question that states excel is stored on a sharepoint folder so it is A and E for me.

Selected Answer: AD
Tried Option A and it works. When you have OneDrive syncing on your machine then this option is simple and Straight forward.

D: Onedrive for business is built on SharePoint Online, hence you can access Ondrive files from SharePoint folder connector

I would vote for A and D options.
Excel connector looks like requiring local gateway and web option in Semantic Model (former Dataset) I just tried is offering "Web API" and "Web Page" available options which doesn't look correct to me

The video is two years old, and it seems that Microsoft has resolved the issue since then. I tested it, and it worked in my case.

https://www.youtube.com/watch?v=CYSMZxaXNLk

Selected Answer: AD
Correct Answer A and D

A. Excel Workbook
D. SharePoint folder

You can use the "Excel Workbook" connector to connect to the Excel file stored in OneDrive and import it into a Power BI dataset. This connector allows you to select the OneDrive folder where the file is located and specify the file name.

You can also use the "SharePoint folder" connector to connect to the OneDrive folder and import the Excel file into a Power BI dataset. This connector allows you to specify the URL of the OneDrive folder and navigate to the Excel file within the folder.

Using either of these connectors ensures that the dataset can be refreshed in Power BI, as the connection to the OneDrive folder will remain active even if the Excel file is updated or moved within the folder.

No confusion, and no need to discuss further

I believe A & C are the right answers. I have my excel file in Onedrive and I connect to the dataset through this means and refresh my dashboards. We can import excel files directly from Onedrive which is cloud. gateway connection isnt needed.

Selected Answer: DE
schedule refresh without a gateway

that is how I understood it as well. As mentioned we have an Excel file in one drive. 
So we would first connect with the SharePoint folder, and then get the Excel from that folder.

Answer: A. Excel Workbook and D. SharePoint folder

Reason:

Excel Workbook (A): Directly connects to an Excel file stored in OneDrive. Power BI supports this connection type, and the dataset can be refreshed in Power BI service.

SharePoint folder (D): Connects to files stored in SharePoint, which includes OneDrive for Business. This allows Power BI to access and refresh the Excel file as long as it's in a SharePoint or OneDrive for Business folder.

Text/CSV (B): Used for CSV files, not Excel files.
Folder (C): Used for multiple files in a folder, not specifically for a single Excel file.
Web (E): Used for web URLs, not for files stored in OneDrive.

Selected Answer: DE
You need to ensure that the dataset can be refreshed in powerbi.com

a and c are correct. it requires physical copies to be imported into the power bi dataset and not some data gateway as in the case of web and sharepoint.

The correct Answer that i am very sure of is E, i always connect my google sheet file to power BI using web option and it always get refreshed on schedule refresh

Selected Answer: DE
Since the File is in excel format and residing in cloud (OneDrive), we can connect to it easily either by SharePoint or Web Connecters.

ChatGPT is wrong A TON!!! Be very careful with that thing.

Chatgpt says the answer is sharepointfolder and web and I agree

Selected Answer: AC
"The file must be imported to a Power BI dataset." that may be the key of this question, as the 4 options can connect to the online file, if want this file can be imported to a Power BI dataset, Sharepoint folder and Web will have some limitations, but Excel Workbook and Folder can definitely be imported to this Power BI dataset. 

Excel Workbook and Folder related documents: 
Connect to a folder from Power Query Online: 
https://learn.microsoft.com/en-us/power-query/connectors/folder#connect-to-a-folder-from-power-query-online 
Connect to an Excel workbook from Power Query Online: 
https://learn.microsoft.com/en-us/power-query/connectors/excel#connect-to-an-excel-workbook-from-power-query-online

Selected Answer: AC
A & C connectors present you the common dialog window allowing you to pickup the file from your ONE DRIVE FOLDER(available from windows explorer).
No credentials needed, no extra authentication

option D&E - The SharePoint folder connector also work for files hosted on OneDrive for Business.

The correct answer is E - Web
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-use-onedrive-business-links

ChatGPT is wrong a ton and DANGEROUS! Seriously. People need to do their homework and recognize ChatGPT for the entertainment piece that it is, and that is all it is.

GPT probably found its answer from this site, unfortunately

The requirement is "You need to ensure that the dataset can be refreshed in powerbi.com". No assumption on what you need to configure so that the dataset is updated. If you're connected through the Web or SharePoint Location, the ability to refresh from Power BI is already available with no additional connection configuration.

Selected Answer: AC
- The biggest difference between AC and DE is about authentication. With AC you already have the OneDrive folder synced to your computer, so once the dataset is published into the Power BI service, no further configuration needs to be made (assume that gateway was installed on your computer).
- On top of that all 4 option is possible, but AC requires the least operation. Since the question does not mention anything about authentication, AC should be the most suitable

This was in the exam today.
I passed the exam with 911 score.
My answer : SharePoint Folder and Web

D and E, because the data needs to be refreshed from PBI Service.

Selected Answer: DE
Connecting via SharePoint folder or Web does not require Gateway service

Selected Answer: DE
Referred to -
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-use-onedrive-business-links

Selected Answer: DE
It's definitely DE as explained from Microsoft instructor

A & D are the two connectors we use to connect to the file.
That is because:
A. Excel Workbook: Power BI supports direct connections to Excel files in OneDrive, allowing you to import data and refresh it in Power BI Service. 
D. SharePoint folder: OneDrive for Business is often integrated with SharePoint, and you can use the SharePoint folder connector to access files stored in a SharePoint document library, which can include OneDrive files.

Selected Answer: DE
In the Excel connector you can see the docmentation says "If the Excel workbook is online, use the Web connector to connect to the workbook."

The answer is D and E.
This is what stands out in the question

 "You need to ensure that the dataset can be refreshed in powerbi.com."

You can sync your one drive with SharePoint folder and then for one drive, no gateway is needed so you can just use web for connecting

ChatGPT doesn't know the question, which is about Excel Workbook but on OneDrive, not on your computer.
The answer is Web & Sharepoint (can by synced with OneDrive).

ChatGPT4:
According to the document, to connect to the Excel file in a Microsoft OneDrive folder for importing to a Power BI dataset, you can use the following two connectors:

Excel Workbook (Option A): Power BI allows you to get data from many types of files, including Excel workbooks. You can select the Excel option when using the Get data feature in Power BI Desktop.

SharePoint - Team Sites (Option D): Saving your Power BI Desktop files to SharePoint Team Sites is similar to saving to OneDrive for Business. The main difference is how you connect to the file from Power BI. You can specify a URL or connect to the root folder.

From reading materials:
According to the document, to connect to a Microsoft Excel file in a Microsoft OneDrive folder and ensure that the dataset can be refreshed in powerbi.com, you can use the following two connectors:

Excel Workbook (Option A): Power BI allows you to get data from many types of files, including Excel workbooks. You can select the Excel option when using the Get Data feature in Power BI Desktop.

SharePoint - Team Sites (Option D): Saving your Power BI Desktop files to SharePoint Team Sites is similar to saving to OneDrive for Business. The main difference is how you connect to the file from Power BI. You can specify a URL or connect to the root folder.

Selected Answer: DE
The answer depends on the requirement to be able to refresh the dataset in Power BI Service - of the choices here, only the Web and SharePoint Folder connectors permit scheduled refresh in PBI Service.

Selected Answer: DE
Surely, when you use SharePoint Folder, you need to use root folder, not subfolders. 
Web is directly connection to excel file

Selected Answer: DE
To connect PowerBi to a one drive folder you need a digital link

Hola de nuevo, es A y E, de acuerdo con este vídeo https://youtu.be/RSh3ZaPUX24

Selected Answer: DE
The question asks us "Dataset can be refreshed in PBI" so when the report published to any other owner's Workspaces it can be refeshed
- Options A - B - C use the local address of your single file/folder although it stay in Onedrive but this is your synced local file/folder
- Options D - E of course using web address (https://)

CHATGPT answer :

To ensure that the dataset can be refreshed in powerbi.com from a Microsoft Excel file in a Microsoft OneDrive folder, you can use the following connectors:

A. Excel Workbook: This connector allows you to connect directly to Excel files and import data from them into Power BI.

D. SharePoint folder: Since the file is located in a Microsoft OneDrive folder, which is often associated with SharePoint, you can use the SharePoint folder connector to connect to the OneDrive folder and import the Excel file.

Therefore, the correct connectors to use in this scenario are A. Excel Workbook and D. SharePoint folder.

Ans: (A)Excel Workbook and (D) SharePoint folder.
 option C (Folder) and option E (Web) are not the appropriate connectors for connecting to an Excel file in a Microsoft OneDrive folder.

Selected Answer: AD
Excel Workbook and Sharepoint are the best methods to connect data in this scenario

The Web connector in Power BI allows you to connect to various web-based data sources, such as web pages, web APIs, and OData feeds. While it is not specifically designed to connect to OneDrive, you may be able to use the Web connector to access OneDrive data if you have a way to authenticate and access the API for your OneDrive account. However, using one of the other connectors specifically designed for OneDrive (such as Excel Workbook or SharePoint Folder) would likely be a more straightforward and efficient way to connect to your Excel file in OneDrive.

web is not recommended for connecting Excel files stored in OneDrive .

The correct connectors to connect to a Microsoft Excel file in a Microsoft OneDrive folder and ensure dataset refresh in Power BI are:

A. Excel Workbook
E. Web

Explanation:
A. Excel Workbook: This connector allows you to connect directly to an Excel file (.xlsx) stored in OneDrive and import data into Power BI. It provides a seamless integration between Excel and Power BI.

E. Web: This connector enables you to connect to a file hosted on the web, including files stored in OneDrive. By providing the URL of the Excel file in OneDrive, you can import its data into Power BI and set up automatic refreshes.

Both these connectors, Excel Workbook and Web, provide the necessary functionality to connect to a file in OneDrive and import it into Power BI while ensuring dataset refresh in Power BI service.

Selected Answer: AD
Excel workbook is in OneDrive and OneDrive is cloud and we do not need a gateway.

Selected Answer: AD
If the Excel file is stored in OneDrive, then the best connectors to use for refreshing the file in Power BI service are:

Excel Workbook connector - This connector allows you to import data from an Excel workbook stored in OneDrive and create a dataset in Power BI. Once the dataset is created, you can publish it to Power BI service and set up scheduled refresh to ensure the data is up-to-date.

SharePoint Folder connector - This connector allows you to access files stored in SharePoint and OneDrive for Business, including Excel workbooks. You can use this connector to import data from an Excel workbook stored in OneDrive, create a dataset in Power BI, and set up scheduled refresh.

If the Excel file is stored locally on your computer or on a network drive, then the best connector to use for refreshing the file in Power BI service is:

Selected Answer: AD
Further clarification

The Web connector in Power BI is not the best option for connecting to an Excel file. There are other connectors specifically designed for connecting to Excel files, such as the Excel Workbook connector and the SharePoint Folder connector.

The Web connector is best suited for connecting to web-based data sources that expose data through a URL or web address, such as web pages, web APIs, and OData feeds.

To connect to an Excel file stored in OneDrive or SharePoint, you should use the Excel Workbook or SharePoint Folder connector, respectively. These connectors are specifically designed to connect to Excel files and allow you to import data from the Excel file and create a dataset in Power BI.

Once the dataset is created, you can then publish the dataset to the Power BI service and set up scheduled refresh to ensure the data is up-to-date.

Guys, How do you connect excel file in one drive via SharePoint ?

AC are correct, tried, one drive is a folder after all.

From chatGPT:

OneDrive is a web-based storage service. However, in the context of this question, option E (Web) is not a suitable connector to use for importing the Excel file to a Power BI dataset because the Web connector is designed for connecting to web-based APIs and web services, and not for importing files from cloud-based storage solutions like OneDrive.

To import an Excel file from OneDrive to Power BI, it is recommended to use the Excel Workbook connector or the SharePoint folder connector, depending on how the file is stored.

Therefore, in this specific scenario, option E (Web) is not the correct answer.

Selected Answer: DE
El archivo de .xlsx se encuentra en una carpeta de OneDrive, por lo que conectarse a ella puede ser atreves de una carpeta SP o Web

Selected Answer: AC
A and C seems correct -- if you connect to your excel file on one drive through Excel connection, the updates on one drive document are reflected on Power BI https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-excel-file-onedrive

Selected Answer: DE
Key Phrase :: Each correct answer presents a complete solution.

Wie so nicht A und C das ist doch viel sinnfoller!

Selected Answer: DE
Otherwise we would only connect locally and it would't be possible to refresh it in power bi service.

a make a test with my personal onedrive space from office 365 logged on my windows 11, then i can catch the excel using excel connectors and folder connector, its work but only personal onedrive, maybe on ONEDRIVE FOR BUSSINESS the answer is other.

This is a very tricky question. The options A and E are correct IF MIcrosoft OneDrive had syncronized with a folder at personal computer... Otherwise the answer is D and E.

Selected Answer: E
I Agree. E is a better solution.

Selected Answer: DE
If data should be refreshed, Then refresh will work when the connectors are web or SharePoint folder. It means, data should be available all time (online). If we use local path, It required a gateways.

Thanks
Karthik

The question is tricky. It stated "OneDrive Folder", not "OneDrive for Business Folder", which turns to Sharepoint and has path. Mere OneDrive has no path at all.

Selected Answer: DE
I have Try them,

A, B, C Totally impossible.

E work
D i got some errors surelly i miss something

Selected Answer: DE
D & E seem to be the consensus

Selected Answer: DE
To keep the dataset on powerbi.com updated, should be online. (I believe also must be considered the right OneDrive version - https://www.microsoft.com/en-ca/microsoft-365/onedrive/onedrive-for-business)

Selected Answer: DE
D and E. that's the way that I actually do on my job

It's DE i think referred from
https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-excel-file-onedrive

Selected Answer: DE
Two options:
- Copy and edit Path of the Excel file then use "Web" Connector: Option E
- Copy and edit Path of the OneDrive folder then use "Sharepoint Folder" connector: Option D
Source: https://www.youtube.com/watch?v=GGHbbg6yi-A

I think the option is Web insted of Excel woorkbook https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-use-onedrive-business-links

You have to ensure that data wil be update. So should will be correct answer Sharepoint folder and web? Because you have to import Excel workbook and folder, but Web and Sharepoint folder update automatically.

A.Excel workbook with web path to the file 
D.Sharepoint Folder with web path to OneDrive (sharepoint) root domain >navigation to file
since it needs to be refreshed online option C will fail as it calls the folder from local path

I dont think so. Where do you find a URL for One Drive

Isn't Web an option as well?
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-use-onedrive-business-links

Selected Answer: AC
A. Excel Workbook
C. Folder

It says Excel file, so B does not meet the requeriment

Not! The question states Excel file not Text/csv

So I agree with the answer, but I believe you could also use the Text/Csv connector. So B should in theory also be a valid answer.

You have a Microsoft Excel file in a Microsoft OneDrive folder.
The file must be imported to a Power BI dataset.
You need to ensure that the dataset can be refreshed in powerbi.com.
Which two connectors can you use to connect to the file? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.

Box 1: 69 -
69 distinct/different values.
Note: Column Distribution allows you to get a sense for the overall distribution of values within a column in your data previews, including the count of distinct values (total number of different values found in a given column) and unique values (total number of values that only appear once in a given column).

Box 2: 4 -
Reference:
https://systemmanagement.ro/2018/10/16/power-bi-data-profiling-distinct-vs-unique/

Different should include Distinct and Unique records

Actually, I tested and "unique" also counts a unique blank cell if you have it

69 and 4 is the correct option:
There are 69 different values in State including nulls
There are 4 non-null values that occur only once in State

For everyone confused, the rule to remember is that Unique values are a subset of distinct values. So, every unique value is a distinct value but distinct values are not always unique.

Given answers are correct. 69 distinct, 4 unique.

69 and 4 are correct.
The column has 69 distinct values, which includes all unique values (4) plus the empty values(nulls).
There are 4 unique values in the column, those are non-null values that occur only once in the "State" column.

Perfect explanation, but correct me if I am wrong this data set would then have 65 null values?

This Column Distribution feature allows you to get a sense for the overall distribution of values within a column in your data previews, including the count of distinct values (total number of different values found in a given column) and unique values (total number of values that only appear once in a given column). In other words, distinct values represent the total number of different values found in a given column, while unique values represent the total number of values that only appear once in a given column.
Cheers!

every unique value is considered as distinct so sum with distinct my friend :)

Why the 4 unique values are not considered district values as well? I didn't get it! Does anyone could help me please? Ty. Answer: 73 and 4

69 DIstinct (DIfferent) values (including null) / 4 UNIque (1 (UNI) appearance each) values

Can anyone share the downloaded pdf for the exam. Because I am writing the exam tomorrow

This was on the exam.
I thought I understood it but now certain I got the first answer wrong.
It helps me to notice that unique values are counted within the distinct values.
So yeah, 69 and 4

Correct. There are 69 different values in the column, 4 of which only occur once.

1. Hay 69 valores diferentes en State, incluidos los valores nulos.
2. Hay 4 valores no nulos que aparecen solo una vez en State.

Hey, the column distribution clearly mentions that there are 69 distinct values out of which 4 values occurs only once. So clearly, its 69 & 4.

73 
4 ABOSOLUTLY CORRECT ..HERE IS THE EVIDENCE distinct in this table tells you the total count of how many values are present, while unique tells you how many of those values only appear once.

There are literally two numbers mentioned in the question oxo.

it will not be counted in case of unique because it occurred 4% I think it happened more than one time

null value is counted in distinct and unique values

Unique represents values that appears just 1 time (Only once) at this column...
 If Null is greater than 1, it counts JUST as a "Distinct" and will NOT change the "Unique Value".
PS: In case of just 1 Null row, it WILL increase the Unique (just +1, no matters how many times it will occured, JUST +1)!!!

..So, 69 different values (including Nulls) are on this Column AND it's not possible to define How many rows it has (so far, so good! this is not required here)

AND, as... Null is GREATER than 1 (just checking the percentage), we conclued that : There are 4 Unique non-nulls values that occured only once in State.
the answer is:
69 for the first and 4 to the last one.

The given answer is correct.
There are 69 different values in State including nulls.
There are 4 non-null values that occur only once in State.

HOTSPOT -
You are profiling data by using Power Query Editor.
You have a table named Reports that contains a column named State. The distribution and quality data metrics for the data in State is shown in the following exhibit.
//IMG//

Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.
NOTE: Each correct selection is worth one point.
Hot Area:
//IMG//

Box 1: Merge -
There are two primary ways of combining queries: merging and appending.
* When you have one or more columns that you'd like to add to another query, you merge the queries.
* When you have additional rows of data that you'd like to add to an existing query, you append the query.

Box 2: Disable the query load -

Managing loading of queries -
In many situations, it makes sense to break down your data transformations in multiple queries. One popular example is merging where you merge two queries into one to essentially do a join. In this type of situations, some queries are not relevant to load into Desktop as they are intermediate steps, while they are still required for your data transformations to work correctly. For these queries, you can make sure they are not loaded in Desktop by un-checking 'Enable load' in the context menu of the query in Desktop or in the Properties screen:
Reference:
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-include-in-report-refresh

The given answer is correct.
Combine the queries by performing a: Merge.
On the Categories query: Disable the query load.

Of course we need to merge the two queries (join them) and we have also to disable the query load. THe asnwer is OK for me

Merge & Exclude the query from report refresh are correct answers. 
Use the merge to combine the "Products" and "Categories" queries based on the "CategoryID" column.
Once we have combined the queries, we don't need the standalone "Categories" query because we have merged its data into the "Product" table. We "Exclude the query from report refresh" to ensure it doesn't unnecessarily reload when refreshing the report.

La respuesta es correcta Combinar, pq se requiere complementar la primera tabla y Deshabilitar la carga de la consulta, para mejor rendimiento.

1. Combine las consultas realizando: Merge - Combinar
2. En la consulta Categorías: Deshabilite la carga de consultas.

This is correct
- Merge
- Disable the query load

I totaly agree with the answer, Merge and disable the category query

correct 
- Merge
- Disable the query load

Usually the import is not a one time excercise and you will want to be able to refresh the datamodel with updated sources. Then you will need the Categories QUERY again.
This first option is about deleting the Categories QUERY, not the Categories TABLE.

I understand the merge and the disable query concept but why don't you delete the categories table after merge

Answer is correct, disabling the query load for Categories will exclude it from appearing as a table.

HOTSPOT -
You have two CSV files named Products and Categories.
The Products file contains the following columns:
✑ ProductID
✑ ProductName
✑ SupplierID
✑ CategoryID
The Categories file contains the following columns:
✑ CategoryID
✑ CategoryName
✑ CategoryDescription
From Power BI Desktop, you import the files into Power Query Editor.
You need to create a Power BI dataset that will contain a single table named Product. The Product will table includes the following columns:
✑ ProductID
✑ ProductName
✑ SupplierID
✑ CategoryID
✑ CategoryName
✑ CategoryDescription
How should you combine the queries, and what should you do on the Categories query? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
//IMG//

.css-1hohgv6{display:-webkit-box;display:-webkit-flex;display:-ms-flexbox;display:flex;-webkit-align-items:center;-webkit-box-align:center;-ms-flex-align:center;align-items:center;-webkit-flex-direction:row;-ms-flex-direction:row;flex-direction:row;gap:0px;}Exam.css-1cp477w{color:#4285F4;}Prepper

Exam
Prepper