About the Power Query category

Workouts Power Query
1

Gain experience through our workout exercises tailored to develop your querying skills and proficiency in Power Query.

WHY PARTICIPATE?

Power Query is one of the best tools available for data cleaning, preparation and transformation. It has a very well-designed UI that can tackle a large number of data problems, as well as in incredibly powerful language (M code, short for “Mash-up”) to handle more advanced transformations and the development of custom functions.

As the Excel BI nightly challenges run by Vijay Verma (a partner with Enterprise DNA) are geared primarily to advanced users, this workout will typically be at a beginner to intermediate level.

HOW TO SUBMIT YOUR SOLUTION?

When you have completed the workout, just post a screenshot of your flow steps and another screenshot of the outcome in the forum thread for that workout.

2

This post is part of the Enterprise DNA platform initiative to provide information about the Workouts | Power Query Category.

To ask any questions on the content covered in this category please start a new topic within the forum. You may check this how-to guide for reference - How To Use The Enterprise DNA Support Forum.

Thank you!

3

let
Source = Excel.CurrentWorkbook(){[Name=“CheckNumbe”]}[Content],
TypeModified = Table.TransformColumnTypes(Source,{{“Name”, type text}, {“Lib”, type text}}),
ExtractCheckNumber = Table.AddColumn(
TypeModified,
“Check Number”,
each
let

    PositionOfNumerAsList = Text.PositionOfAny ([Lib],{"0".."9"},Occurrence.All),
    PositionOfFirstNumberOccurrence = List.Min(PositionOfNumerAsList),
    PositionOfLastNumberOccurrence  = List.Count(PositionOfNumerAsList),
    CheckNumber                     = Text.Range([Lib],PositionOfFirstNumberOccurrence,PositionOfLastNumberOccurrence)
    in

  Text.PadStart(CheckNumber,7,"0")

  

)

in
ExtractCheckNumber