• Zur Hauptnavigation springen
  • Skip to main content
  • Zur Hauptsidebar springen
  • Zur Fußzeile springen

THE SELF-SERVICE-BI BLOG

Wir lieben Microsoft Power BI

  • Live Online Power BI-Schulungen
  • Beratung
    • Was ich für Dich tun kann
    • Showcases
  • Kunden
  • BLOG
    • Business Topics
    • Technical Topics (english)
    • Tools
  • Podcast
  • PUG Hamburg
    • PUGHH Anmeldung
    • PUGHH Recordings
    • PUGHH (interner Bereich)
    • Power BI UserGroup bei Xing
  • Kontakt
  • Über

Technical Topics (english) / 10. Juni 2020

3 ways for sums over columns in Power Query

Normally I calculate sums in Power Query over rows. Recently, however, I was given the task of calculating sums over columns. I wrote a German-language post about this here and used mainly functionalities of the UI.
Power Query guru Bill Szysz has commented this post (even though it was in German) and sent me another alternative solution via email. He gave me his permission to share this solution with you here. In addition, I already had a conversation with my friend and Power Query guru Imke Feldmann about another alternative solution, which I have also included in this article. Happy learning 🙂 You can download the sample file here.

The goal

The goal is to create a row at the end of the table that represents the total for the month columns and contains the row label ‚Total‘ in the first column.

Alternative Solutions

Imke and Bill have sent me their solutions, but the comments of the source code are from my pen. With this I wanted to make it easier for you as a reader and I hope that I didn’t make any mistakes. If you find any mistakes here, it’s on me.

Solution #1 – by Bill Szysz

Solution #2 – by Bill Szysz

Solution #3 – by Imke Feldmann

Conclusion

There are many roads to the solution. These 3 here, seem very creative to me and show quite different ways to solve the problem. With the available (relatively small) amount of data, the 3 methods seem to be almost equally fast. Of course this would have to be tested with larger amounts of data, but that’s not what this post is about :-). I thank Imke and Bill a lot for their solutions. I have learned a lot. Which of them do you find the most exciting?

Cheers from Hamburg, Germany

Lars

Lars Schreiber

Lars ist Berater, Entwickler und Trainer für Microsoft Power BI. Er ist zertifizierter Power BI-Experte und Microsoft Trainer. Für sein Engagement in der internationalen Community wurde Lars seit 2017 jährlich durch Microsoft der MVP-Award verliehen. Lies hier mehr…

Kategorie: Technical Topics (english) Stichworte: Excel, Power BI Desktop, Power Query

Neueste Kommentare

  • Zeki Aslan bei So kalkulierst Du Durchschnittswerte über Zeitreihen mit DAX
  • Alain Clémençon bei Flexible Perioden in Power BI vergleichen
  • Thomas Reick bei HR-Report: Mitarbeiterbestand immer im Blick
  • Eva bei Den Daten- und Aktualisierungsstand in Power BI immer im Blick
  • Lars Schreiber bei Schritt für Schritt zu Nettoarbeitstagen mit Power Query

Leser-Interaktionen

Kommentare

  1. sandeep pawar meint

    6. Juli 2020 um 8:53 pm

    Lars, I dont see the solutions in the blog. I just see below. Do I need to download a file?
    Solution #1 – by Bill Szysz

    Solution #2 – by Bill Szysz

    Solution #3 – by Imke Feldmann

    Antworten
  2. mma173 meint

    12. Juni 2020 um 12:16 pm

    Thanks‘ for sharing this. There was lots to learn from the solutions provided by the very well recognized PQ gurus. It was a very good exercise too.

    Here is the solution that I came-up with:
    let
    Source = Excel.CurrentWorkbook(){[Name=“Datenbasis“]}[Content],
    ToSum = {„January“,“February“, „March“, „April“, „May“, „June“, „July“, „August“, „September“, „October“, „November“, „December“},
    Sums = List.Transform (Table.ColumnNames(Source), each if _=“Cost centre“ then „Total“ else if List.Contains(ToSum,_) then try List.Sum(Table.Column(Source, _)) otherwise _ else null),
    SumTable = Table.PromoteHeaders(Table.Transpose(Table.FromColumns({Table.ColumnNames(Source), Sums}))),
    Combine = Table.Combine({Source,SumTable})
    in
    Combine

    Antworten

Schreibe einen Kommentar Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Haupt-Sidebar

WEBSITE DURCHSUCHEN

Awards

Tweets

Tweets von @SchreiberLars

series of posts

Lists, Records and Tables in M - how, when and why
Writing Documentation for custom M functions
The Environment concept in M for Power Query in Excel and Power BI Desktop
Creating asymmetric Pivots and control them by Slicer

Footer

Kontakt

THE SELF-SERVICE-BI BLOG
Mail: lars@ssbi-blog.de

Rechtliches

  • Impressum
  • Datenschutz

Über THE SELF-SERVICE-BI BLOG

Ich bin ein freiberuflicher Power BI-Berater, -Entwickler und -Trainer und wurde von Microsoft mehrfach mit dem MVP Award ausgezeichnet. Ich arbeite mit einem kompetenten Netzwerk aus freiberuflichen und ambitionierten Kollegen zusammen. Erfahre hier mehr.

Social

Folge mir...

Copyright © 2023 · Digital Pro on Genesis Framework