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

Поделиться
HTML-код
  • Опубликовано: 24 дек 2024

Комментарии • 17

  • @KimzCraftz
    @KimzCraftz Год назад +2

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

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  Год назад

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

  • @IvanCortinas_ES
    @IvanCortinas_ES Год назад

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

  • @kebincui
    @kebincui Год назад +1

    Excellent as always. Thanks Mark👍🌹

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  Год назад

      Thanks Kebin, as always, I appreciate your support.

    • @kebincui
      @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"

  • @GrainneDuggan_Excel
    @GrainneDuggan_Excel Год назад +1

    Nice on Mark! ❤

  • @grahamparker7729
    @grahamparker7729 Год назад

    This was a great video, thanks.

  • @xplover
    @xplover Год назад

    i need your help to creat formula please and thank you

  • @dennisclark6548
    @dennisclark6548 Год назад

    Brilliant!

  • @stefankirst3234
    @stefankirst3234 10 месяцев назад

    Very cool 😀 Thanks a lot!

  • @danishnawaz3651
    @danishnawaz3651 10 месяцев назад

    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