Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Aydeedglz
Helper V
Helper V

Summarize table by column

Hi, I have the next table, where I have quarters and past due data. Instead of having multiple rows with "Qtr 2 2022, Qtr 3 2023, Qtr 1 2019, etc..." I want to sum the total value of each one, how can I do that?

 

Aydeedglz_1-1669314797974.png

 

 

1 ACCEPTED SOLUTION
KNP
Super User
Super User

So try this.

Right click create new query >> blank query

KNP_0-1669325438510.png

 

Go to 'Advanced Editor' for this query and select all and paste in the below. 

Then follow these steps again for option 2. Pick the one that works best for you. I think option 2.

 

Option 1

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45WCjRUMDIwMlSAAkOlWJ1oJTBBlowRSMYAJmNKhgxu00yQZIxBMkZwPQbkSKE4Ao+UCYorUBxILalYAA==",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [Column1 = _t]
    ),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "")),
    #"Extracted First Characters" = Table.TransformColumns(
        #"Filtered Rows", {{"Column1", each Text.Start(_, 7), type text}}
    ),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted First Characters", {{"Column1", "Quarter"}}),
    #"1" = Table.AddColumn(#"Renamed Columns", "1", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"2" = Table.AddColumn(#"1", "2", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"3" = Table.AddColumn(#"2", "3", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"4" = Table.AddColumn(#"3", "4", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"5" = Table.AddColumn(#"4", "5", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"Grouped Rows" = Table.Group(
        #"5",
        {"Quarter"},
        {
            {"1", each List.Sum([1]), type number},
            {"2", each List.Sum([2]), type number},
            {"3", each List.Sum([3]), type number},
            {"4", each List.Sum([4]), type number},
            {"5", each List.Sum([5]), type number}
        }
    )
in
    #"Grouped Rows"

 

 

Option 2

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45WCjRUMDIwMlSAAkOlWJ1oJTBBlowRSMYAJmNKhgxu00yQZIxBMkZwPQbkSKE4Ao+UCYorUBxILalYAA==",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [Column1 = _t]
    ),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "")),
    #"Extracted First Characters" = Table.TransformColumns(
        #"Filtered Rows", {{"Column1", each Text.Start(_, 7), type text}}
    ),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted First Characters", {{"Column1", "Quarter"}}),
    #"1" = Table.AddColumn(#"Renamed Columns", "1", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"2" = Table.AddColumn(#"1", "2", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"3" = Table.AddColumn(#"2", "3", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"4" = Table.AddColumn(#"3", "4", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"5" = Table.AddColumn(#"4", "5", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"5", {"Quarter"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(
        #"Unpivoted Other Columns", {"Quarter", "Attribute"}, {{"v", each List.Sum([Value]), type number}}
    )
in
    #"Grouped Rows"

 

I think this should give you a better understanding of what I was trying to explain.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

20 REPLIES 20
KNP
Super User
Super User

So try this.

Right click create new query >> blank query

KNP_0-1669325438510.png

 

Go to 'Advanced Editor' for this query and select all and paste in the below. 

Then follow these steps again for option 2. Pick the one that works best for you. I think option 2.

 

Option 1

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45WCjRUMDIwMlSAAkOlWJ1oJTBBlowRSMYAJmNKhgxu00yQZIxBMkZwPQbkSKE4Ao+UCYorUBxILalYAA==",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [Column1 = _t]
    ),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "")),
    #"Extracted First Characters" = Table.TransformColumns(
        #"Filtered Rows", {{"Column1", each Text.Start(_, 7), type text}}
    ),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted First Characters", {{"Column1", "Quarter"}}),
    #"1" = Table.AddColumn(#"Renamed Columns", "1", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"2" = Table.AddColumn(#"1", "2", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"3" = Table.AddColumn(#"2", "3", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"4" = Table.AddColumn(#"3", "4", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"5" = Table.AddColumn(#"4", "5", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"Grouped Rows" = Table.Group(
        #"5",
        {"Quarter"},
        {
            {"1", each List.Sum([1]), type number},
            {"2", each List.Sum([2]), type number},
            {"3", each List.Sum([3]), type number},
            {"4", each List.Sum([4]), type number},
            {"5", each List.Sum([5]), type number}
        }
    )
in
    #"Grouped Rows"

 

 

Option 2

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45WCjRUMDIwMlSAAkOlWJ1oJTBBlowRSMYAJmNKhgxu00yQZIxBMkZwPQbkSKE4Ao+UCYorUBxILalYAA==",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [Column1 = _t]
    ),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "")),
    #"Extracted First Characters" = Table.TransformColumns(
        #"Filtered Rows", {{"Column1", each Text.Start(_, 7), type text}}
    ),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted First Characters", {{"Column1", "Quarter"}}),
    #"1" = Table.AddColumn(#"Renamed Columns", "1", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"2" = Table.AddColumn(#"1", "2", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"3" = Table.AddColumn(#"2", "3", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"4" = Table.AddColumn(#"3", "4", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"5" = Table.AddColumn(#"4", "5", each Number.RandomBetween(0.5, 15.3), Decimal.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"5", {"Quarter"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(
        #"Unpivoted Other Columns", {"Quarter", "Attribute"}, {{"v", each List.Sum([Value]), type number}}
    )
in
    #"Grouped Rows"

 

I think this should give you a better understanding of what I was trying to explain.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
KNP
Super User
Super User

Select the column/s that you want to group by, right click and select 'Group by'.

See gif.

GroupBy.gif

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Thanks, if a want them all summarized by the quarter column?

Sadly, you have to click the 'Add Aggegation' and manually add every column you want to summarize.

You can do it in the 'Advanced Editor' in the code if that is quicker for you. Depends on what your copy/paste/replace skills are like. 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

This is what I got

 

Aydeedglz_1-1669316711876.png

 

Aydeedglz_2-1669316718632.png

 

As far as the error is concerned, I think your "Over 90 Q" is missing a 'type nullable number', that might be causing that error. 

I'll reply to your other comment separately.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

If that doesn't help, you may need to explicitly set the type of the columns in the LND_SPF_PDREPORT to make sure it is set to decimal.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

But that is to add another column I want to summarize, the example is in my latest post 

This is what I am trying to have, that is why I need to have only ONE row of every quarter, no add new columns  

 

Aydeedglz_0-1669316553119.png

 

I'm not sure if I fully understand, but I think if you right click on your 'Qtr Year Query' and select, 'Unpivot other columns' I think your data will be in a better format to work with.

Do that before the 'Group by' step.

 

KNP_0-1669317166504.png

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Right niw I have 999+ rows where in the column Qtr Year Query, where I have just a few categories (Qtr 1 2019, Qtr 1 2020, Qtr 1 2021, etc...) what I want is ONLY HAVING ONE ROW for each quarter

 

Combine al the rows of the same quarter insted of having a lot of them

 

Aydeedglz_0-1669317352274.png

Aydeedglz_1-1669317395444.png

 

Example: This what I have

Quarter        1-30

Q1 2021        1

Q1 2021        1

Q1 2021        1

Q2 2020        5

Q2 2020        5

Q2 2020        1

Q2 2020        4

Q3 2022        10

Q3 2022        10

Q3 2022         5

Q3 2022         5

Q4 2020         1

Q4 2020         1

Q4 2020         1

Q4 2020         1

 

This is what I want

Q1 2021        3

Q2 2020        15

Q3 2022        30

Q4 2020        4

 

 

 

 

 

Based on my understanding, I still think you need to do the unpivot, then the group by, including both your Qtr and new Attribute column that is created by the unpivot. i.e. select both columns and then select group by.

 

KNP_0-1669318587332.png

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

I am still having the same error, but I don't have any nulls

Can you please paste some sample data that includes all the columns that matter to you?

Change anything you need to if it is sensitive info.

I'll try and create the steps I'm talking about.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

I have found this, how do I delete the nulls?

 

Aydeedglz_1-1669324572565.png

 

If you don't need those rows.

Change the '= null' in the formula bar to '<> null'.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Hi for some reazon I can't post the data I have but is 5 columns with random values in the rows, the principal quarters

 

This is still my result

 

Aydeedglz_0-1669324162598.png

 

This is an example

 

Qtr Year Query1-30_M31-60_M61-90_Mover 90_MPast Due_M
Qtr 2 202000.00013770.01186560.4012253250.413228625
Qtr 2 2020-0.1767325320.366745447450.0621927210.557510224
Qtr 2 20200.5717017310.528761148450.50935743278
Qtr 2 20200.061242530.08200334451878
Qtr 2 20200.382714565451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 3 202045451878
Qtr 3 20204545180.0228349
Qtr 3 20204545180.01342654
Qtr 3 20200545180.0007731
Qtr 3 2020054518-0.000774
Qtr 3 20200545180.00904846
Qtr 3 2020054518-1.99082946
Qtr 3 20200.0003545180.003044
Qtr 3 20200545180.00307582
Qtr 3 20200545180.0000975
Qtr 3 20200545180.001762892
Qtr 3 20200.001469189545180.012166495
Qtr 3 20200545180.029644206
Qtr 3 20200.0040421090.0038243745180.011887887
Qtr 3 202000.00591694345180.007293917
Qtr 3 202000450.0073484090.007348409
Qtr 3 20200000.0067769670.006776967
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 202245180.02283497688
Qtr 4 202245180.013426547688
Qtr 4 202245180.00077317688
Qtr 4 20224518-0.0007747688
Qtr 4 202245180.009048467688
Qtr 4 20224518-1.990829467688
Qtr 4 202245180.0030447688
Qtr 4 202245180.003075827688
Qtr 4 202245180.00009757688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688

Hi, this is a small recreation 

 

Qtr Year Query1-30_M31-60_M61-90_Mover 90_MPast Due_M
Qtr 2 202000.00013770.01186560.4012253250.413228625
Qtr 2 2020-0.1767325320.366745447450.0621927210.557510224
Qtr 2 20200.5717017310.528761148450.50935743278
Qtr 2 20200.061242530.08200334451878
Qtr 2 20200.382714565451878
Qtr 2 2020-0.000344995451878
Qtr 2 202005451878
Qtr 2 20200.00055451878
Qtr 2 2020-0.001502975451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 2 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 202045451878
Qtr 3 20204545180.0228349
Qtr 3 20204545180.01342654
Qtr 3 20200545180.0007731
Qtr 3 2020054518-0.000774
Qtr 3 20200545180.00904846
Qtr 3 2020054518-1.99082946
Qtr 3 20200.0003545180.003044
Qtr 3 20200545180.00307582
Qtr 3 20200545180.0000975
Qtr 3 20200545180.001762892
Qtr 3 20200.001469189545180.012166495
Qtr 3 20200545180.029644206
Qtr 3 20200.0040421090.0038243745180.011887887
Qtr 3 202000.00591694345180.007293917
Qtr 3 202000450.0073484090.007348409
Qtr 3 20200000.0067769670.006776967
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 20224518787688
Qtr 4 202245180.02283497688
Qtr 4 202245180.013426547688
Qtr 4 202245180.00077317688
Qtr 4 20224518-0.0007747688
Qtr 4 202245180.009048467688
Qtr 4 20224518-1.990829467688
Qtr 4 202245180.0030447688
Qtr 4 202245180.003075827688
Qtr 4 202245180.00009757688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688
Qtr 4 20224567667688

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.