Excel Power Query Tutorial - Add Conditional Column

The Excel Power Query "Add Conditional Column" feature is the focus of Part 4 of my Power Query tutorial series. In this video I demonstrate how to add a conditional column to consolidate rare categories into more generalized categories useful for advanced analytics.

-----------------------------------------------------------------------------------------------------------------------
Video Resources
-----------------------------------------------------------------------------------------------------------------------
Get the Excel workbook used in this video:

-----------------------------------------------------------------------------------------------------------------------
Additional Power Query Tutorial Videos
-----------------------------------------------------------------------------------------------------------------------
What is Excel Power Query AND Why it Matters to You! (Part 1):

Excel Power Query - Add Column From Examples (Part 2):

Excel Power Query - Add Custom Column 1 (Part 3):

Excel Power Query - Grouping Tables of Data (Part 5):

Excel Power Query - Transforming Nested Tables (Part 6):

-----------------------------------------------------------------------------------------------------------------------
Excel Analytics Training
-----------------------------------------------------------------------------------------------------------------------
👩‍🔬👨‍🔬 Learn to use your basic Excel skills to analyze the business like a Facebook data scientist:

💥 Learning R programming is easy for Excel users! 💥

Stay healthy and happy data sleuthing!

#ExcelPowerQuery #PowerQueryTutorial #PowerQuery #PowerQueryAddConditionalColumn

[DaveOnData]

👩‍🔬👨‍🔬 Learn to use your basic Excel skills to analyze the business like a Facebook data scientist:

[shanabenjamin]

Thanks! You clarified the issues I had!❤

[andrewdove]

working with data is like taking your brain to the gym, feels good!

[CzechCzar]

Incredibly useful. I am currently learning Excel PowerQuery for analytics at my job, and this provided a really great explanation

[paulfarmer]

Enjoying your content a lot . You should point out in video that you couldn’t use the age column to determine adultness because in prior video you commented age field has a lot of missing records . Age would have been more natural field to use and wouldn’t required complex conditional logic to group adultness and sex together and then you could have avoided logic required to deal with female doctor ( this complex conditional logic will also make queries run slower if you were dealing with massive dataset) but in this case would not have given as wide a statistical coverage in determining if that data dimension has explanatory power. Important to note i feel if you have just watched this video in isolation, you would be left wondering as to why you went this more complex way of using title to infer adultness.

[garciarogerio]

So far very good. Looks similar to the Query editor of Power Bi

[AkshayA-kjzk]

hi, actually i have a doubt while in conditional column i am not able to fetch the suitable column under column name. can u please provide solution for this.

[davebennett]

Nice video. I haven’t really bothered with power query as I didn’t know that it could do this stuff. I’ve been doing the same sort of categorisation by adding calculated columns in the power pivot model. If you build a separate lookup table in the power pivot model (in your example it would contain two columns: Value and Output) and then add a calculated column to your main table with a formula which uses the DAX sequence. This works like a Select statement in SQL with a join to another table, or explained in excel terms it’s a super lookup function which allows you to look up on as many criteria as you want. Advantages are that this is easy to understand what the criteria are by viewing the lookup table. Also, it’s easily expandable to more criteria and using multiple lookup tables. Dax is weird to learn (coming from either an excel or sql background) but very useful for this type of data manipulation when you want to keep everything in an excel environment.

[ickorling]

Dude, nice! The relation to higher level structure and planning of the data base filtering anomolies-- that part.

Helped me realize there's more... flexible method of outputting even simple data charts in lieu of shoddy pivots and manual lookup formulas.

Can that power query thing compile into SQL automatically with Table names used through out in excel?

[CDDCDj]

Great video. Sorry for going out of topic, do you know how to make a desktop direct access icon for an app in r? Thanks for sharing your knowledge.

[jbj]

Is this video along the lines of data mining in the sense that titles with similar characteristics are grouped together?

[statistikochspss-hjalpen]

11:00
Does the order matter? Why does the Alice Leader condition need to come first?