Power Query Column Profiling for Text Length Analysis

Profile and clean the data

DRAG DROP You use Microsoft Power BI Desktop to preview a query in Power Query Editor. The query contains a column named Location Name. You need to identify the maximum length of each value in the Location Name column without changing the data. Which actions should you perform in sequence? To answer, drag the appropriate actions to the correct order. Each action may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point. exhibit

Answer Select the Location Name column, set Column Profiling based upon entire dataset, from the View tab select Column profile, and group the value distribution by text length.

Community Insight

The exam traps candidates into selecting 'Transform' steps (like Extract > Length) which modify the dataset; the correct approach relies entirely on the 'View' tab's profiling features which are read-only.

This question tests the correct sequence of actions in Power Query Editor to profile a column's text length without modifying the underlying data. The page establishes that using the View tab's Column Profile and Distribution tools is the non-destructive method required by the scenario.

Learners often select 'Extract > Length' from the Transform tab because it calculates length, but this adds a new column or modifies the query result, violating the 'without changing the data' constraint.

Community Discussion (9 comments)

e68b25f 👍 1

From the View tab, select Column profile. Set Column Profiling based upon entire dataset. Select the Location Name column. Group the value distribution by text length. Official Microsoft source: Microsoft Learn — Using the data profiling tools⁠

Idaresit 👍 1

1.Select the Location Name column 2.From View tab, select Column profile (entire dataset) 3.From View tab, select Column distribution 4.Group value distribution by text length “Transform → Extract → Length” 🚫 This is WRONG because: It changes the data Question explicitly says “without changing the data” So we avoid Transform steps completely.

Abos140 👍 2

This should be the correct sequence; Step 1 : From the View tab, select Column profile Step 2 : Set Column Profiling based upon entire dataset Step 3 : Select the Location Name column Step 4 : Group the value distribution by text length

omath 👍 3

1. Select the Location Name columna - You must first highlight the column you want to analyze. 2. Set Column profiling based on entire dataset - By default, Power Query only profiles the first 1,000 rows. - To ensure accuracy, go to View → Column profiling → Based on entire dataset. 3. From the Transform tab, select Extract → Length - With the column selected, go to Transform → Extract → Length. - This creates a temporary column showing the number of characters in each row’s value. 4. From the View tab, select Column profile (or Column distribution) - Column distribution shows the frequency of lengths. - Column profile gives detailed statistics, including minimum, maximum, and average length. - Either will let you identify the maximum length of values in the column.

AmieM 👍 1

Step 1: You need to select the column you're analyzing. (Select the Location Name column) Step 2: Ensures Power Query analyzes all data, not just the first 1,000 rows. Step 3: Use Extract > Length to calculate the character length of each value. (From the Transform tab, select Extract, and then select Length.) Step 4: Column profile gives detailed statistics, including min/max length, for analysis purposes only. (From the View tab, select Column profile.)

AmieM 👍 1

For Step 3 & 4, Column profile shows stats about the existing data — min, max, count, etc. But it doesn’t calculate or display length of text values unless you’ve first created a column with the length, hence why step 3 should be (From the Transform tab, select Extract, and then select Length.) before selecting column profile.

Lulu_2022 👍 2

✅ Ordered Steps: 1. Select the Location Name column This ensures that Power Query focuses the profile and distribution stats on the correct column. 3. From the View tab, select “Column profile” Displays detailed insights like min/max length, value distribution, and counts (nulls, errors, etc.). 4. Group the value distribution by text length This is a manual step: you can extract text length (Transform → Extract → Length), then group by that length column to see how many values share the same character count.

Koriza 👍 3

Step 1 should be Column Profile not distribution as we need value distribution with ability to change 'group by'

225401f 👍 15

I think Step 1 : Select the Location Name column Step 2 : Set Column Profiling based upon entire dataset Step 3 : From the View tab, select Column profile Step 4 : Group the value distribution by text length

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Expert Analysis

Why the Answer Is Correct

To identify statistics like maximum length without altering the source data, you must use Power Query's profiling tools. First, select the specific column ('Location Name') to target the analysis. Second, ensure the profiling scope is set to the 'entire dataset' via the View tab options, as default profiling may only sample rows. Third, open the 'Column profile' pane to see aggregate stats like min/max length. Fourth, switch to the 'Column distribution' view and group by 'Text length' to see the frequency distribution of lengths. This sequence provides all necessary insights non-destructively.

Why Other Candidate Answers Are Wrong

Options involving 'Extract > Length' or similar Transform actions are incorrect because they permanently add calculated columns to the query output, which changes the data structure. Options that skip selecting the column first will result in generic profiling of the entire table rather than the specific column. Skipping the 'entire dataset' setting may lead to inaccurate max length values if the longest value exists outside the sampled rows.

Community Comment Notes

Community consensus strongly favors the View-tab-only approach. User 225401f correctly identified the sequence: Select Column -> Set Entire Dataset -> Column Profile -> Group by Text Length. User e68b25f confirmed this order with official Microsoft Learn references. Comments by AmieM and others suggesting 'Extract > Length' were widely corrected by users like Idaresit, who emphasized that Transform steps violate the 'without changing data' rule.

Official Reference

https://learn.microsoft.com/en-us/power-query/column-profile

Exam Strategy

Always read the 'without changing the data' constraint carefully in PL-300 Power Query questions. If the goal is analysis or inspection, look for 'View' tab options like Column Profile or Distribution. If the goal is to add a new metric to the dataset, then look for 'Transform' options.

Frequently Asked Questions

Does Column Profile change the data in Power Query?

No, Column Profile is a read-only analytical tool located in the View tab. It generates statistics about the selected column without adding any columns or altering the query results.

Why is 'Extract Length' incorrect for this question?

Extracting length is a transformation step that creates a new column containing the character count. This modifies the data model, violating the requirement to not change the data.

More PL-300 FAQ →

Related Analysis

Practice All PL-300 Questions

Access 116 questions with complete answers and detailed explanations.

View Full PL-300 Practice Test →

← Back to PL-300 Study Guide