Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Value and Identifier in 2 Columns

I'm pretty sure this is rather easy but I cant figure out the best terms to search for the solution.   I have numerical values that I want to sum but their "identifier" is next to the value in anot...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZQxa8NADIX/i+dUSLqTfDeWzIEOJR2CBw8mNYQWnAT686sEN6T125rtTjL69J503u2a7TB9vpyn4e19PA3NqjGnWjUOz/vxcD4mXh+Gfop7SuRS4/A6Ho/nYdOfpvErrlKI0yV+qbTp98PHqX/axl0zmUvTrXb3qUjw/PE9Vgs5G8CqUlJbYiuZ6q34LwVKYqhUysQmQIFRdQEKpCUtBiGpEiuySZ1KbpeQQsaOGKFPICJncmsxQjKwJBDJAKKSO1ZhiZQrssoCgYYd25EzklEoW4EQJtUEGLwsz6AyY3OUXDPqXEhrAZ0LZYWdK5lge2L+bUX2xIaJLxk/4cVLuMaxN5LQFv3LG6HUImsk1h85M4cXxlzjuGvm8uiuo6g/3Ap++N7FxArSHn+7ClZCmKwk5C7H68b25kq1oLWLRKtggrf4X8ic6Lpv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Model Material 1" = _t, #"Model Material 1 Usage" = _t, #"Model Material 2" = _t, #"Model Material 2 Usage" = _t, #"Model Material 3" = _t, #"Model Material 3 Usage" = _t, #"Model Material 4" = _t, #"Model Material 4 Usage" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Model Material 1", type text}, {"Model Material 1 Usage", Int64.Type}, {"Model Material 2", type text}, {"Model Material 2 Usage", Int64.Type}, {"Model Material 3", type text}, {"Model Material 3 Usage", Int64.Type}, {"Model Material 4", type text}, {"Model Material 4 Usage", Int64.Type}}),

    cols=Table.FromColumns({List.Combine(List.Alternate(Table.ToColumns(#"Changed Type"),1,1,1)),List.Combine(List.Alternate(Table.ToColumns(#"Changed Type"),1,1,0))},{"material","qty"}),
    #"Grouped Rows" = Table.Group(cols, {"material"}, {{"sum", each List.Sum([qty]), type nullable number}})
in
    #"Grouped Rows"

 

 

 

 

in your local environment the result should looks like

 

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

 

I tryied to use the "type table" expression to set  the column name and also the values type, but seem doesn't work:  in the following expression the value of column qty are seen as text values  and not as number, and error is raised.

 

 

 

 

 

 

 

PS

does anyone have any idea where (meaning in what part of the function it can be generated) this error message comes from?

Why does it refer to a difference operation between two values?

 

PPS

I believe the reason lies in the way the groupby function works internally and this error message reveals this internal aspect