Forum Discussion
Enforcing Data Limits/Requirements in Power Query
Hi - I'm new to using Power Pivot/Power Query, but I have some files that are too large for standard Excel and I don't feel like diving straight into Access, so Power Query it is. With that out of th...
MicrosoftMacros2011 In PowerQuery (PQ) you can select a column. Then on the "Add column" tab, choose Extract, Length. This will create a new column with the lengths of the texts in the original columns. Now you can easily filter out the ones that exceed 50.
You don't mention anything about what the other columns characteristics are, so I can't say how to check those. But very likely you can do it in a similar way. Perhaps you have learn to write some M-code as well. Once you have set this up correctly, you can connect to new data and refresh the query (or queries) over and over again.
Hmm sounds like looking into some M-Code might be best eventually, but also sounds like Power Query doesn't have something out of the box. An additional column is what I would do in Excel and use the LEN function; but I usually only do that if I'm concerned something exceeds a length and I wouldn't want to do an extra column per actual column. Especially not if I eventually needed 1 column per "characteristic." I don't have all of the requirements in front of me, but it's your standard gamut of restrictions: specific lengths, some alpha/numeric, some only numeric, one column has specific text that each value needs to match and only 4 possible options, etc.