How to Think & Write Complex M Easily || Power Query Case Study

In this video, we will explore how to think and write complex M in Power Query while tackling a data cleansing problem. Whether you encounter a similar problem or not, you will find valuable insights and techniques that you can apply to your own data sets.

Through this M language solution, we will discuss the logic and steps involved in problem-solving.

#powerquery #mlanguage #datacleansing #excel #datamanipulation #powerbi #mcode #advancedexcel #datatransformation #problemsolving

===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -

✔️ Power Query Course-

✔️ Master Excel Step by Step-

✔️ Business Intelligence Dashboards-

===== LINKS 🔗 =====

===== CONTACT 🌐 =====

===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!

[pk]

You are really great bruh. I have worked with much complex case and most of the time I have solved it using the UI part with different combination since I am unaware of the power of M. After watching your watching now my brain whenever there is a requirement it thinks how to solve it in M rather than using UI.


Great thanks for you!!!

[tinhoyhu]

This is magic. Not only is the build process magical, but the clear explanation tying it all together.

[chrism]

You are a PQ genius, you really are. Thanks Chandeep!

[VijayKumar-zdou]

Hi Chandeep,

Can you assist me with how to fetch historical data for NSE stocks from the Equity Pandit website using Power Query, with date references from cells? We need a video demonstrating this process for educational purposes. Please help us understand how to fetch historical data for multiple NSE stocks with date cell references using Power Query from the Equity Pandit website🙏🙏🙏

[andreascharalambides]

Nice one, Sharing your logic provides huge value. Any black Friday sales on the courses?🙂

[murmursoy]

Great vid as always. 👍
My Solution:
// List-to-rows -> pivot -> rename column names
let
table1 = <<table with [Letter] and [Day]>>,
table2 = <<table with [Days]>>,
dayslistadded = Table.AddColumn(table2, "DaySplit", each Text.Split([Days], ", ")),
dayslisttorows = Table.ExpandListColumn(dayslistadded, "DaySplit"),
// Once use table1[Letter] and you have the perfect order without any absent columns!
pivoted = Table.Pivot(dayslisttorows, table1[Letter], "DaySplit", "DaySplit", each if _ = {} then null else true),
// Sadly not applicable if you have same [Days] values ...
roworderreset = Table.Sort(pivoted, each List.PositionOf(table2[Days], [Days])),
weekdaynameexpanded = Table.RenameColumns(roworderreset, Table.ToRows(table1))
in
weekdaynameexpanded

[diegocollerig]

Ah, it's very easy!😂Great job! Thanks for your videos! However, there are no shortcuts. We have to study and try and try again.

[pthapa]

Amazing example, thanks for bringing this with us.

[flyingHubby]

I really love your videos, but this solution is over complicated.
This does it all and uses pretty simple UI commands.

- split to list
- expand
- join on the lookup column
- add "true" column
- pivot
- order columns in the seq of the lookup table
done

[carolshipley]

Brilliant as usual. Can you suggest where this solution can be used for any other problem?

[vinovici]

What a guy. Man I appreciate you and these videos.

[mienzillaz]

As soon i saw the problem I paused the video and gave it a go.


let

Source = Excel.CurrentWorkbook(){[Name="Dayz"]}[Content],

Initial = Table.AddColumn(Source, "MappedDays", (OT)=> Table.PromoteHeaders(Table.Transpose(Table.SelectColumns(Table.TransformColumns(Table.SelectRows(Letterz, each List.Contains(Text.Split(OT[Days], ", "), [Letter])), {"Letter", each true}), {"Day", "Letter"})))),

#"Expanded MappedDays" = Table.ExpandTableColumn(Initial, "MappedDays", Letterz[Day], Letterz[Day])

in

#"Expanded MappedDays"

[HappyAnalysing]

Hi sir,
Your videos are so informative and very helpful to many people like me. Thank you so much for making such an awesome videos.
Could you please do a video on org chart on power bi for HR data when there are 30+ employees involved in 4-5 levels. Please help.. I’ve been searching a lot on this and didn’t find any helpful article or video on this. Could you please do a video on this. This will be very helpful.
Thank you!

[rickychen]

Nice thought process! I would assume it will be easier to achieve the same result using DAX, and Using M vs DAX is something that I always think about/struggle with when tackling a problem. Appreciate it if you could do a video about that thanks

[Hello-bnyc]

I come away from watching your videos feeling very defeated. I would love to do half of what you do.

[buchlotnik]

let
f=(x)=>Excel.CurrentWorkbook(){[Name=x]}[Content],
g=(x)=>[a=List.Transform(Text.Split(x{0}, ", "), (x)=>Record.Field(dict, x)),
b=Record.FromList({x{0}}&List.Repeat({true}, List.Count(a)), {"Days"}&a)][b],

b = f("base"),
dict = Record.FromList(b[Day], b[Letter]),
to = Table.FromRecords(Table.ToList(f("tbl"), g), {"Days"}&b[Day], MissingField.UseNull)
in
to

[jerrydellasala]

Big thumbs up! Great solution, great explanation! 👍👍

[tak]

Thanks Chandeep for breaking down your problem solving approach and steps. Indded, sharing your logic provided even more value than the solution.

[hussainmeghani]

Sir, while click on content... Detail displayed but file name cloning remove.... How to fix the problem... I am extract csv format file through power query

[satishshastri-zt]

How to pull data correctly if Thursday letter is kept as 'T' rather 'R'? As lookup source will be same for both Tuesday and Thursday. Please provide this lookup solution, i'm facing this problem and lookup chooses very first option only as i know.