Converting Semi-Structured Data to Tables
A sales operations team gives you a file containing customer orders. Power BI accepts the connection, Power Query opens, and the preview contains data. Does that prove the source is ready for analysis? Not necessarily.
An order identifier might be visible, while the customer details remain inside a record and the purchased products remain inside a list. The connection works, but the business still doesn’t have a useful table. The important evidence isn’t that Power BI reached the file. It’s that each required business value has been transformed into the intended rows and columns, with the right data types and enough identifying information to trace the result back to an order.
That distinction matters for the PL-300 skill in this lesson’s scope, which is to convert semi-structured data to a table. The requirement starts with the desired analytical result, not with a particular button. You need to understand what the source contains, which values the business needs, what one resulting row should represent, and how you’ll verify that the transformation preserved the meaning of the data.
Semi-structured data has organization, but it doesn’t necessarily arrive as one flat rectangle. JavaScript Object Notation, usually called JSON, can contain named values, records, lists, and objects nested inside other objects. Other semi-structured sources can present similar shapes. A top-level order might contain a simple order identifier and order date.
Its customer value might be a record containing a customer identifier and region. Its items value might be a list containing several product records. Power BI can connect to that source, but those nested structures still have to be expanded and typed into tabular columns before relational modeling.
This is a data-preparation responsibility, so Power Query is the owning Power BI layer. Power Query is Power BI’s data acquisition and transformation environment. Its formula language is called M. You don’t repair a nested source by writing a report measure or changing a chart. Data Analysis Expressions, or DAX, can calculate over data available in the semantic model, and a visual can present that data, but neither one substitutes for shaping nested source values into the rows and columns the model requires.
Suppose the team needs an order-level table for monitoring fulfillment. The required columns are order identifier, order date, customer identifier, customer region, and order status. The incoming source also contains a nested list of items, internal processing metadata, and several optional shipping details.
Expanding every available field just because it exists would ignore the requirement. For this table, you expand the customer fields the business needs, retain the order identifier, and leave unrelated nested content out of this particular output. You then assign appropriate data types to the resulting columns rather than assuming that every value arrived ready for analysis.
The stable order identifier is especially important. It gives you a way to connect transformed values to the source-level business entity. If a region is missing or a status looks wrong, you can locate the affected order rather than staring at an anonymous row. A stable key can also help you compare records before and after transformation. Not every source provides an ideal key, so the careful rule is to preserve one where possible, not to pretend that every document contains one.
Now consider the nested item list. One order may contain three product records while another contains one. If the business asks for product-level sales analysis, the required table shape differs from the order-level fulfillment table.
Expanding the list means accounting for its multiple members, so the resulting rows must still retain the order identifier that tells you which order each item came from. The correct choice follows the intended grain, meaning what one row represents. An order-level requirement and an order-item-level requirement shouldn’t be treated as though they were the same table simply because they originated in the same JSON document.
This is why there isn’t one universally correct expansion for every semi-structured source. One analyst might need customer attributes at the order grain. Another might need individual purchased items.
A third might not need the nested shipping object at all. The reliable decision rule is to expand only needed fields, preserve a stable key where possible, and recheck your schema assumptions when nested structures can vary. That rule is more useful than memorizing an interface location because it connects the transformation to the business meaning.
The neighboring Power BI layers still matter, but they answer different questions. Power Query determines how the source is acquired and transformed. The semantic model organizes the resulting tables for analysis. DAX evaluates calculations using model data and its calculation context.
The report layer controls how results are presented and interacted with. The Power BI service handles concerns associated with publishing, refresh, access, and consumption. If the source still contains an unexpanded customer record, changing a report visual is work in the wrong layer. The visual can’t make a missing model column appear through presentation alone.
Imagine that a second analyst reports that the sales solution is finished because the query preview shows rows. You inspect the output and find a column whose values are still records. Another column contains lists. The preview is evidence that some source content has been read, but it isn’t evidence that the requested table exists.
The expected behavior is a tabular query output containing the required business fields at the intended grain. The actual behavior is a partially shaped structure. That comparison identifies a Power Query problem before anyone starts changing relationships, DAX calculations, or report filters.
You can make the diagnosis more precise by starting with one known order. Record its order identifier and the expected customer values. If product-level output is required, note how many item records belong to that order.
Then follow that order through the transformed query output. Are the required customer fields represented as ordinary columns? Is the order identifier preserved?
Do the product rows remain attributable to that order? Do the output values use the intended data types? These observations test the result of the transformation rather than merely confirming that transformation steps exist.
An edge case provides a stronger test. Perhaps most customer objects include a region, but some do not. Or most orders contain item records with the fields you expect, while a later delivery varies in its nested structure. A transformation that appears correct for the first few records may rely on a schema assumption that doesn’t hold for every refresh. Rechecking those assumptions means examining whether the required fields are consistently available and whether variations produce an acceptable output instead of silently undermining the table’s meaning.
The response isn’t automatically to expand everything. Additional fields can bring additional assumptions and complexity without helping the business question. Instead, return to the requirement.
If region is required, its absence deserves investigation and an explicit data-quality decision. If an optional internal metadata field isn’t needed, leaving it out avoids making the table depend on irrelevant source content. Selective expansion keeps the transformation focused while still acknowledging that the nested source may evolve.
Data types are part of the conversion, not a cosmetic step after it. A flattened column name may look correct while its values are still represented with an unsuitable type. An order date needs to behave as the intended date value.
An identifier should be treated according to its role as an identifier rather than assumed to be a quantity just because its characters happen to be numeric. An amount intended for numerical analysis needs a suitable numeric type. The research basis for this lesson requires nested values to be expanded and typed before relational modeling, so both structure and type belong in your verification.
Return to the sales and operations requirement. You decide that the solution needs two query outputs because the business has two distinct analytical grains. The first output represents orders and contains the required order, customer, and status fields.
The second represents order items and retains the order identifier alongside the needed item fields. The source’s nested organization doesn’t dictate that every value must be forced into one oversized table. Your business requirement determines which tabular outputs are useful, while the stable identifier preserves meaning as nested content is expanded.
Before accepting those outputs, compare expected and actual row behavior. For the order-level output, a known order should be represented at the intended order grain. For the item-level output, the known order’s item records should be represented at the intended item grain and remain associated with that order.
If an order with three items produces an unexpected result, investigate the list expansion and retained key at the query layer. Don’t compensate by creating a DAX expression that hides the incorrect shape. A calculation over incorrectly prepared data doesn’t repair the preparation decision.
Column profiling and direct inspection of query output can provide observable evidence about the transformation. Look for required columns, unexpected nulls, type problems, and values that remain structured when they should have been expanded. Compare a few known source entities with their resulting rows. The purpose isn’t to trust a preview blindly.
It’s to test whether the output satisfies the stated grain, fields, identifiers, and types. Successful configuration tells you that Power Query accepted what you did. Quality checks tell you whether what you did produced the required table.
Downstream behavior gives you another view of the same issue. If the semantic model expects an order identifier but the transformation dropped it, later model design loses the key needed to preserve the business connection. If a required numeric value remains unsuitable for numerical analysis, calculations may not behave as intended. If one row unexpectedly represents an entire list rather than an individual item, a report can’t reliably present item-level results. These downstream symptoms may appear in the model or report, but their cause can still be the earlier Power Query shape.
That’s an important troubleshooting habit. Observe where the symptom appears, then identify which layer owns the mechanism that could cause it. A report visual may reveal that product-level results are missing, but if the product records never became tabular rows, the corrective action belongs in Power Query. Conversely, if the query output is correct and the problem appears only under a particular model or report condition, you shouldn’t keep changing the source transformation without evidence. Validate each layer where its behavior occurs.
Refresh is another condition worth testing because a query that works against one sample can encounter different nested content later. Suppose the initial orders all include the same customer fields, but a subsequent delivery omits an optional field or presents a different nested combination. Reproduce the failing condition if possible, capture the expected and actual output, and inspect the query step directly responsible for expanding that structure. After correcting the assumption, retest both the original records and the variation. Fixing the edge case shouldn’t damage the normal case that already worked.
This method is more disciplined than changing unrelated settings until the error disappears. Start with a specific symptom. Perhaps a required customer column is missing after refresh, an item can no longer be traced to its order, or a value has an unexpected type. State what should have happened and what actually happened.
Then inspect the transformation associated with that field or nested structure. Correct the cause and repeat the comparison. A successful refresh is useful evidence, but it still needs to be paired with inspection of the refreshed output because completion alone doesn’t prove business correctness.
The same standard applies when there’s no visible error. A transformation can complete and still produce an unsuitable table. It might include fields the report doesn’t need, omit the stable key, flatten the wrong nested object, or create a grain that differs from the requirement.
None of those failures requires a dramatic error message. That’s why verification must be designed around expected data behavior. Ask what one row means, which fields must be present, which type each field requires, and how a record can be traced to its source entity.
Be careful with the phrase looks right. A report page can look plausible even when its underlying table has a structural defect. Totals based on an incomplete item expansion may still appear neat. A customer label may display while the customer identifier needed for dependable analysis was discarded.
Presentation quality doesn’t replace query validation. For this topic, the strongest first evidence is the transformed query output. Model behavior and report results can then confirm that the prepared table supports the intended downstream use.
A familiar feature is not automatically the right feature. If your requirement is to expose nested source values as tabular columns and rows, Power Query owns that transformation. A semantic-model relationship can connect prepared tables, but it doesn’t expand an object still trapped inside a source structure.
DAX can calculate a result from model data, but it isn’t a substitute for converting the source into the required table. A report interaction can filter what a consumer sees, but it doesn’t redefine the source grain. Recognizing these boundaries keeps troubleshooting focused.
You can now judge the original sales file with evidence rather than optimism. The connection is only the beginning. For the order-level requirement, inspect whether the selected customer and status values became typed columns while the order identifier remained intact.
For the item-level requirement, inspect whether the needed list members became the intended rows and still carry that identifier. Test a normal order, an order with multiple items, and a record whose nested structure varies. Then confirm that the resulting tables support the model and report behavior the business requested.
This preparation also shapes later design decisions. Cleanly transformed outputs make it possible to reason about what each table represents and how business entities remain identifiable. Poorly transformed outputs force later layers to work around ambiguity that should have been addressed during preparation. The current certification scope asks you to convert semi-structured data to a table, but applied judgment means more than producing columns. It means choosing the right fields, preserving meaning through keys and grain, assigning types, and proving that the output remains correct when the source isn’t perfectly uniform.
Converting semi-structured data into a table begins with a business definition of the table you actually need. Power Query is where nested records, lists, and objects are expanded and typed before relational modeling, but expansion should remain deliberate. Keep the fields that serve the requirement, preserve a stable key where possible, and check that each resulting row has the intended meaning.
Most importantly, don’t treat a working connection, a completed refresh, or a plausible report as proof. Inspect the query output, compare known source entities with their resulting rows, and test variations in the nested structure. When the output has the required fields, types, grain, and traceability, you have evidence that the transformation supports the Power BI solution rather than merely appearing to work.
Turn a nested record or list into a table by describing the structure before the steps. What missing or irregular element would you use as an edge-case test? The matching episode reference turns the spoken reasoning into a compact set of terms and hands-on tests. This lesson was produced by Bare Metal Cyber dot com.