Grouping and not having to specify the aggregated columns

[OKgo]

I would like to pivot this list to be one row = one ID

The dates are out of my control in the data set and change frequenty. There are far more columns. 

ID	26/30/2023	26/30/2025	1/1/2026
801	54	null	null
803	38	null	null
804	0	null	null
804	null	1	null
804	null	null	1
805	12	null	null
806	4	null	null
903	0	null	null

I tried vairous pivots, transposing with no luck.

With grouping, - the PQ gets hard coded with dates which won't work with the live data:

{"ID"}, {{"6", each List.Sum([#"26/30/2023"]), type number}}

Any tips apprecaited. 

[edhans]

Is this what you are looking for?

What I did:

1. Selected the ID and then Unpivoted other column. That automatically got rid of the nulls.
2. Then I pivoted the Attribute columm back and told it not to aggregate the values.

Full M code here.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjAwVNJRMjUBEnmlOTkwKlYHJGUM5BpbYJUCaTDAKQMVNcQpA1cAkTEFsY2wGmcG5GJznSXYdRhOiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"26/30/2023" = _t, #"26/30/2025" = _t, #"1/1/2026" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"26/30/2023", Int64.Type}, {"26/30/2025", Int64.Type}, {"1/1/2026", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

[OKgo]

Agh. Workign through that with you made me realize I need a tag to come with me for the numbers to make sense

ID	Tag	26/30/2023	26/30/2025	1/1/2026
801	Tag 1	54	null	null
803	Tag 1	38	null	null
804	Tag 1	0	null	null
804	Tag 2	null	1	null
804	Tag 3	null	null	1
805	Tag 1	12	null	null
806	Tag 1	4	null	null
903	Tag 1	0	null	null

So the headers need to be something like "ID" "Tag1_26/30/2023" and so on.

Starting again is an option as I have the data in this format for all 110 items

Site ID	Tag	Date	Qty
801	Tag 1	26/30/2023	1
801	Tag 1	26/30/2023	1

[edhans]

I do not understand. Can you post a screenshot from Excel or something of a mockup of what the data is supposed to be in the end? And I don't understand the 2nd table at all. Why are the quantities for both 1, when 801 has a quantity of 54, and why is the date repeated.

[Anonymous · Accepted solution]

this is my attempt to interpret the request

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY5LCgAxCEPv4rqL8cfMXUrvf43RktZCwYA8Ykzv9D1MjSSUM9oiGrKQgykcSYuAvgfKY85FFNSuB7bDlscRxlwuR4Ui86XMVmH1O1/OPJxH3/ED", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, d1 = _t, d2 = _t, d3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"d1", Int64.Type}, {"d2", Int64.Type}, {"d3", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"total", each tot(_)}}),
    #"Expanded total" = Table.ExpandRecordColumn(#"Grouped Rows", "total", {"d1", "d2", "d3"}, {"total.d1", "total.d2", "total.d3"})
in
    #"Expanded total"

function tot(table):

let
    tot=(tab)=>
    let
    ncols=Table.ColumnCount(tab),
    cols=List.Skip(Table.ColumnNames(tab)),
    values=Table.ToColumns(tab),
    tots=List.Transform({1..ncols-1}, each List.Sum(values{_}))
    
in
    Record.FromList(tots,cols)
    in tot

[OKgo]

This works great for the first post. 

Fot the second post including the tag it will not work. I saught some help and made it work with the "tag" feild. The general principals of making things dynamic and not hard coding is and area I am going to read up on. Many thanks

[OKgo]

I pasted both into a blank Query1 and Query2. They create fine. Thank you. 

Query1
(this does not have the Tag column in my second reply. Sorry for the inconvenience. 

Expression.Error: The name 'tot' wasn't recognized. Make sure it's spelled correctly.

Drill in to

Expression.Error: The import tot matches no exports. Did you miss a module reference?

Query2

I tired to input Query1 to invoke the fucntion. 

I am missing something very obvious... I am beginning to think to move everything to dax. Keep the tables seperate with a relationship and write some very basic measures. It might be so much easier

[Anonymous]

mmmhh peraphs the problem is the name of the query containing the function.

 

[Anonymous]

the same goal can be achieved by changing the @edhans  code as follows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjAwVNJRMjUBEnmlOTkwKlYHJGUM5BpbYJUCaTDAKQMVNcQpA1cAkTEFsY2wGmcG5GJznSXYdRhOiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"26/30/2023" = _t, #"26/30/2025" = _t, #"1/1/2026" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"26/30/2023", Int64.Type}, {"26/30/2025", Int64.Type}, {"1/1/2026", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,2,Replacer.ReplaceValue,{"26/30/2025"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"ID"}, "Attribute", "Value"),
    #"Pivoted Column1" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column1"

[Anonymous]

this is just for fun and to try features that are new to me

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY5LCgAxCEPv4rqL8cfMXUrvf43RktZCwYA8Ykzv9D1MjSSUM9oiGrKQgykcSYuAvgfKY85FFNSuB7bDlscRxlwuR4Ui86XMVmH1O1/OPJxH3/ED", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, d1 = _t, d2 = _t, d3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"d1", Int64.Type}, {"d2", Int64.Type}, {"d3", Int64.Type}}),

    cols=Table.ColumnNames(#"Changed Type"),
    ncols=Table.ColumnCount(#"Changed Type"),
    lst=List.Accumulate({1..ncols-1}, "",(s,c)=>s& "{""" & cols{c}& """, each List.Sum(["&cols{c}&"]) },"),
    expr="{"&Text.BeforeDelimiter(lst,",",{0,RelativePosition.FromEnd})&"}",
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, Expression.Evaluate(expr,#shared))
in
 #"Grouped Rows"

I don't know if such a solution is really useful in real cases.