How to create chart data from Power Query | Excel Off The Grid

[@KimzCraftz]

Amazing tutorial, many thanks for sharing the code also, Mark. I'm finding the Academy membership really beneficial.

[@ExcelOffTheGrid]

Thanks Kim. That is great to hear 😁
This code will make its way into the version of the Quick Steps functions library.

[@danishnawaz3651]

Plz make Video for below Formula "Power Query", Video Time Fram is Max 10mint
1. List.RemoveMatchingItems
2. List.ReplaceMatchingItems
3. List.TransformMany
4. List.ReplaceValue
5. List.PositionOfAny
6. List.PositionOf
7. List.NonNullCount
8. List.Intersect
9. List.Durations
10. List.Difference
11. List.FindText
12. List.InsertRange
13. List.Union

[@kebincui]

Excellent as always. Thanks Mark👍🌹

[@ExcelOffTheGrid]

Thanks Kebin, as always, I appreciate your support.

[@kebincui]

@@ExcelOffTheGrid hi Mark. The customer function fxRepeatValueToNull is very good and very creative. As you know, quite some people are struggling with List.Accumulate function. For those who prefers to us normal and M code to achive the same, I revised the later part of your code by using adding index column. It can achive the same result. The code is as belows, for fun :):
let
Source = Excel.CurrentWorkbook(){[Name="ChartData"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Region", type text}, {"Size", type text}, {"Value", Int64.Type}}),
#"Calculated End of Month" = Table.TransformColumns(#"Changed Type",{{"Date", Date.EndOfMonth, type date}}),
#"Inserted Month Name" = Table.AddColumn(#"Calculated End of Month", "Month Name", each Date.MonthName([Date]), type text),
#"Extracted First Characters" = Table.TransformColumns(#"Inserted Month Name", {{"Month Name", each Text.Start(_, 3), type text}}),
#"Inserted Quarter" = Table.AddColumn(#"Extracted First Characters", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Added Prefix" = Table.TransformColumns(#"Inserted Quarter", {{"Quarter", each "Q" & Text.From(_, "en-GB"), type text}}),
#"Inserted Year" = Table.AddColumn(#"Added Prefix", "Year", each Date.Year([Date]), Int64.Type),
#"Grouped Rows" = Table.Group(#"Inserted Year", {"Date","Year","Quarter","Month Name", "Region"}, {{"Total Value", each List.Sum([Value]), type nullable number}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Region]), "Region", "Total Value", List.Sum),
#"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Date", Order.Ascending}}),
#"Grouped Rows1" = Table.Group(#"Sorted Rows", {"Year"}, {{"AddingYearIndex", each Table.AddIndexColumn(_,"YearIndex",1,1)}}),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows1",{"AddingYearIndex"}),
DynamicColumnNames1 = Table.ColumnNames( Table.Combine( #"Removed Other Columns"[AddingYearIndex])),
#"Expanded AddingYearIndex" = Table.ExpandTableColumn(#"Removed Other Columns", "AddingYearIndex", DynamicColumnNames1, DynamicColumnNames1),
#"Added Conditional Column" = Table.AddColumn(#"Expanded AddingYearIndex", "YearFinal", each if [YearIndex] = 1 then [Year] else null),
#"Grouped Rows2" = Table.Group(#"Added Conditional Column", {"Year", "Quarter"}, {{"YearQuarterIndex", each Table.AddIndexColumn(_,"YearQuarIndex",1,1) }}),
#"Removed Other Columns1" = Table.SelectColumns(#"Grouped Rows2",{"YearQuarterIndex"}),
DynamicColumnNames2 = Table.ColumnNames(Table.Combine(#"Removed Other Columns1"[YearQuarterIndex])),
#"Expanded YearQuarterIndex1" = Table.ExpandTableColumn(#"Removed Other Columns1", "YearQuarterIndex", DynamicColumnNames2, DynamicColumnNames2),
#"Added Conditional Column1" = Table.AddColumn(#"Expanded YearQuarterIndex1", "FinalQuarter", each if [YearQuarIndex] = 1 then [Quarter] else null),
#"Sorted Rows1" = Table.Sort(#"Added Conditional Column1",{{"Date", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Year", "Quarter", "Date", "YearIndex", "YearQuarIndex"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"YearFinal", "FinalQuarter", "Month Name", "North", "South", "Central", "East"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"YearFinal", Int64.Type}, {"FinalQuarter", type text}, {"Month Name", type text}, {"North", Int64.Type}, {"South", Int64.Type}, {"Central", Int64.Type}, {"East", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"YearFinal", "Year"}, {"FinalQuarter", "Quarter"}, {"Month Name", "Month"}})
in
#"Renamed Columns"

[@IvanCortinas_ES]

Exceptional. Thank you very much for this great work with the custom function. Very good tutorial Mark.

[@ExcelOffTheGrid]

Thanks Ivan, I'm glad you enjoyed it.

[@GrainneDuggan_Excel]

Nice on Mark! ❤

[@ExcelOffTheGrid]

😁

[@grahamparker7729]

This was a great video, thanks.

[@ExcelOffTheGrid]

Thanks Graham, I'm glad you enjoyed it. 👍

[@dennisclark6548]

Brilliant!

[@ExcelOffTheGrid]

Thanks - I hope you can put it to good use.

[@stefankirst3234]

Very cool 😀 Thanks a lot!

[@ExcelOffTheGrid]

Glad you liked it!

[@xplover]

i need your help to creat formula please and thank you