Fixed-Width Text Files in Power Query: a Dynamic Solution

Added: 2026-06-03

[00:00:09][music] >> Sometimes a text file lands on your desk and it looks like this.

[00:00:17]No commas, no obvious separator.

[00:00:20]When that happens, here's the first thing to try. Head over to the view tab and check monospace. If the columns suddenly line up perfectly, you're dealing with fixed-width data and that changes everything. Because fixed-width data follows a pattern and once you can identify that pattern, you can build a dynamic solution.

[00:00:43]In this video, I'll show you how to do that.

[00:00:46]Hi, my name is Melissa and it's good to have you here.

[00:00:50]If you want to follow along, there's a link below in the description to my GitHub. That's where I've shared all the resources for this video and I'd also love to hear from you. What kind of data challenges face the most or which part of working with Power Query still feels difficult or unclear?

[00:01:08]Leave a comment and I'll use that to shape future videos. All right.

[00:01:13]There are several ways to solve the scenario that we're facing today, but in this video, we're going to build just one.

[00:01:21]So, most people hard code column positions when working with files like this and when the layout is consistent, that will work just fine. But when you get different reports from that same source system, you can end up building a solution from scratch for each one.

[00:01:40]Instead, if you give Power Query enough instructions so it can determine key pieces automatically, you're creating building blocks, code that can be reused for similar tasks.

[00:01:53]Looking at our sample, there's a perfect candidate to solve this puzzle.

[00:01:58]Do you see the divider row on line eight?

[00:02:02]It shows exactly where every column begins. If we can determine the boundaries, we can use that to split all of the rows into columns.

[00:02:11]Other things I've noticed are this. So, all of the data resides in a single column, and that column is named column one.

[00:02:19]Above our divider row, you will find headers such as product, description, and so on.

[00:02:26]Looking closer, you'll find it's split across two rows, but we'll only use the bottom one.

[00:02:34]And judging by the currency symbol and decimal separator, we're dealing with a US locale.

[00:02:40]Now, to make this work, we need to collect a few ingredients.

[00:02:45]Extract the header text to create column names, extract that divider to determine column positions, and row length to filter the relevant rows of data.

[00:02:56]Once we have that, we can build a splitter and apply it to the entire file.

[00:03:02]First, let's find the header row.

[00:03:05]And no, we're not going to hardcode a row number. We're going to tell Power Query to locate the row that contains a specific text, something that appears first in the header, like product, for example.

[00:03:22]Don't be alarmed, but we'll be building most of our solution in the need funds editor.

[00:03:27]So, let's open that up.

[00:03:30]Personally, I could like to create my custom variables all the way at the top, so above the source tab.

[00:03:37]It's a simple way to organize the steps within your query.

[00:03:42]So, let's give that first variable a name, and I'll call it header.

[00:03:48]Call a function table.skip.

[00:03:52]So, what you need to know about the table skip function is that it will keep skipping rows as long as a condition is true, and it will stop processing your table when the condition returns false.

[00:04:05]So, here we go.

[00:04:07]Provided the source table.

[00:04:12]And then a condition.

[00:04:20]Pass it the name of our column. So, inside of square brackets, call column one.

[00:04:30]And the thing to look for, in this case, product.

[00:04:39]Do you see that red line below source on line six? That's because the end of this line doesn't have a comma yet.

[00:04:47]So, watch. If I place a comma there, that red line will go away.

[00:04:53]So, let's check out the result of this step.

[00:05:02]So, the header row is now the first row in our table.

[00:05:06]And of course, you can right click that first row to drill down and extract that value, or you could write the code manually.

[00:05:15]Let's place my cursor all the way at the end, and the first thing that we'll do is apply item access to access that first row of data. Now, remember, positions in M are zero-based.

[00:05:30]Next, we'll apply field access to access one specific field. You know, we are going to write column one in a set of those square brackets. Here we go.

[00:05:47]Hit tick.

[00:05:49]Perfect.

[00:05:50]So, this extracts the header as a single text.

[00:05:55]We will leave that for now and repeat the same process to also extract the divider row. So, let's head back to the advanced editor.

[00:06:04]So, that divider is key because it will give the structure of the table and we can just copy that line of code here.

[00:06:13]Press control C.

[00:06:15]I'll hit enter and press control V to place that back.

[00:06:20]So, there we go. We need to change two things. First, make sure that the variable name is unique. So, we will call this step divider.

[00:06:33]And next, we need to change what we are looking for. That's product here. I'll double click that.

[00:06:40]And we're going to look for two dashes followed by a space.

[00:06:46]Let's press done.

[00:06:49]And check out the result.

[00:06:54]To filter this document, we can perform a simple check. Does the length of our row, the row that we're iterating, does that match the length of the divider?

[00:07:05]Because we're dealing with fixed width data, all valid values will have the same length. Let me illustrate. I'll add a custom column through that mini table icon there.

[00:07:17]Add custom column. It's just going to be temporary to illustrate something, right? So, text length.

[00:07:26]Pass it column one by just double clicking that.

[00:07:30]Okay.

[00:07:32]As you can see, all of the valid rows have a total length of 127 characters.

[00:07:41]So, I'm going to create a variable to store this length. Here we go.

[00:07:46]Let's get rid of that.

[00:07:48]And let's head back to the advanced editor.

[00:07:52]I go to the end of that line and hit enter. And let's call this row length.

[00:07:59]I'll use the same function that you saw me use just a second ago, text length, and then pass it the divider.

[00:08:16]Time to build a key piece of logic.

[00:08:19]Let's take another look at the table, all right? So, our logic is a bit of code that's going to take our divider and turn that into something that Power Query can use to split all of the data.

[00:08:36]That something is going to be a list of positions.

[00:08:41]The first position is no mystery at all.

[00:08:43]Everything starts at the front, so at the beginning, at index position zero.

[00:08:48]But all of the other columns starts start after a space character, as you can see on line eight.

[00:08:56]So, if we can identify the position for each space, we can offset that value by one, so we have a new column start.

[00:09:07]Let's turn that into a custom function.

[00:09:11]I'm going to call that find starts.

[00:09:21]And to initialize a function, I'll add a set of parentheses. Now, we need to pass that a text string, so we need one parameter there.

[00:09:31]Then the goes to sign. Now again, we know the first position, right? So, as a list, we're going to give that first fixed position, combine it with another list using the ampersand. That's the combination operator.

[00:09:48]Next, we're going to look for all of those space characters and offset that value. Now, to transform values in a list, we use a function called list.transform.

[00:10:02]Look for all of the spaces, text.position of.

[00:10:07]Pass it the thing to process, which is our parameter as and the thing to look for, a space.

[00:10:15]And now we're going to ask for all occurrences of spaces, occurrence.all.

[00:10:23]And when we have all of those positions, we're going to add one to them each, underscore, plus one.

[00:10:34]Add a comma.

[00:10:36]Let's think this true because the process to offset a position manually means that we could land on a space character.

[00:10:47]In that case, we want to eliminate that position.

[00:10:54]We'll use a function list.select to test if we're not landing on a space character.

[00:11:24]Let's test this function by passing the divider.

[00:11:41]>> And check the result.

[00:11:44]Looks good.

[00:11:46]Now we can create a splitter that uses these positions to cut every string at that precise location.

[00:11:54]A splitter is just a function that returns a function. And the function it returned can be invoked for every row in this file.

[00:12:04]Let's head back to the advanced editor.

[00:12:09]Let's call that splitter.

[00:12:13]And call the splitter function.

[00:12:20]Pass the positions.

[00:12:23]All right.

[00:12:25]So before we apply this splitter to the data, let's test it on the header. That should give us a list with all of the column names.

[00:12:46]Okay, these headers need a bit more work. And we can process all of the items using list.transform to trim all of the excess spaces in there.

[00:12:58]So list.transform and trim those spaces.

[00:13:11]There's one more problem to address.

[00:13:13]Column names must be unique. The bottom two values aren't, and that's because originally the headers were split across two rows.

[00:13:22]But that's okay because we can rename them by replacing the value at their position and here's how that works.

[00:13:33]We'll call the function list.replace range.

[00:13:44]Given the position of the value that we want to replace, which is six.

[00:13:52]Then we want to replace two values and now we can provide it a list with two replacement values.

[00:14:01]Let me copy that in.

[00:14:05]And hit that tick.

[00:14:06]At this point, we have everything we need. A row length, a splitter, cleaned header names. It's time to start to transform the actual data.

[00:14:19]So a good place to start is limiting the rows to only those that you actually need.

[00:14:25]Now to filter them, I'll use two conditions.

[00:14:29]The row length and the row start.

[00:14:33]Because that needs to be equal to the letters PC, the product code prefix.

[00:14:40]Here we go.

[00:14:43]This time, I'm going to create a new variable below the source step because this is the actual transformations that we're doing.

[00:14:52]Let's call that filter.

[00:14:55]And we'll call table.select rows.

[00:15:01]Pass it the source table and give it a condition.

[00:15:07]Each text.length needs to be equal to the row length and the text should start with PC.

[00:15:38]To preview the result, we need to return filter below the in clause. So, I'm going to copy that and put it here.

[00:15:52]Now, we can split everything using our splitter and provide clean header names.

[00:16:01]Enter a comma at the end of that line, press enter.

[00:16:05]Let's call that split and call the table.split column function.

[00:16:16]Pass it filter, the column we want to split, our splitter function, and headers.

[00:16:36]Return that after the in clause so we can see the result.

[00:16:45]There's one more thing to do because all of these values are still text. So, the final step is to set column data types.

[00:16:54]I want to remind you that this file was created using a US locale.

[00:16:59]However, my operating system uses a different locale.

[00:17:04]I will need to handle that. You might, too. So, let's see what happens.

[00:17:11]We'll set this to a whole number.

[00:17:16]Set this to fixed decimal.

[00:17:20]And that errors out. That's okay for now. So, you can just ignore that and proceed setting all of the other columns to fixed decimal as well.

[00:17:38]So, the difference in locale between what was used to create the file and what my system used when processing it is the reason why we get all of these errors.

[00:17:50]But, it's easy to remedy.

[00:17:53]I'm just going to expand the formula bar here so we can actually see it. There's an optional parameter called culture.

[00:18:01]And we can provide that a culture [clears throat] tag to tell it what locale was used to create the file. Here we go.

[00:18:09]English US.

[00:18:12]Now, when I hit that tick, all of the errors go away.

[00:18:16]Let's rename this step.

[00:18:26]Done.

[00:18:27]The key to dealing with fixed-width data is identifying the pattern.

[00:18:32]Once you have that, you can build a dynamic solution.

[00:18:36]I hope you've enjoyed this one. Thank you so much for watching. Till next time.