Forum Discussion
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...
Anonymous , I came up with a more generic way of conversion if your dataset consists of more pairs of Material/Usage,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkstyg8oLUoNz8gsSVXSUTI107O0NAIyHNMzc0qLjQ2cc1ITi4B8Y2M9M0NLpVgdiB7fxPTUvJJEoIQBEKObYmShZ2ZgCleMYoGRnqGpKTYLTPQMTA2x6jG21DMwwuYoIzM9CxNzrHpMTPTMTM2x6zE0AbotFgA=", 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]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Model", each Text.Select([Attribute], {"0".."9"})),
#"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1, Int64.Type),
#"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Model", "Index"}, {{"Count", each Record.FromList([Value], {"Material", "Usage"})}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Index"}),
#"Expanded Count" = Table.ExpandRecordColumn(#"Removed Columns", "Count", {"Material", "Usage"}, {"Material", "Usage"})
in
#"Expanded Count"I dont think this shows the final output I am looking for.
Essentially, this data is a report generated by our 3D printer showing how much material is used of each type. There could be the same material in different bays of the machine. Each row is an individual project/print.
I want to be able to take all this data and essentially add up the "Model Material # Usage" of each material. As shown below, VeroPureWhite was in multiple material bays.
Model Material 1 Model Material 1 Usage Model Material 2 Model Material 2 Usage Model Material 3 Model Material 3 Usage Model Material 4 Model Material 4 Usage VeroPureWhite 56.992 Agilus30Clear 33.619 TissueMatrix 18.039 VeroMagenta-V 24.561 VeroMagenta 0 VeroPureWhite 28.605 Agilus30Clear 22.325 TissueMatrix 9.522 VeroPureWhite 52.155 Agilus30Clear 34.051 TissueMatrix 15.961 VeroMagenta-V 17.285 VeroPureWhite 39.022 Agilus30Clear 26.847 TissueMatrix 8.506 VeroMagenta-V 12.31 VeroPureWhite 44.657 Agilus30Clear 26.145 TissueMatrix 8.535 VeroMagenta-V 9.665 VeroPureWhite 53.209 Agilus30Clear 35.149 TissueMatrix 16.944 VeroMagenta-V 18.458 VeroPureWhite 0.223 Agilus30Clear 0 TissueMatrix 0 VeroMagenta-V 0 VeroPureWhite 42.624 Agilus30Clear 31.298 TissueMatrix 11.424 VeroMagenta-V 12.515 VeroPureWhite 17.799 Agilus30Clear 2.116 TissueMatrix 2.116 VeroMagenta-V 2.116 VeroPureWhite 0.132 Agilus30Clear 0 TissueMatrix 0 VeroMagenta-V 0 VeroPureWhite 1.374 Agilus30Clear 1.058 TissueMatrix 1.058 VeroMagenta-V 1.058 VeroPureWhite 0.008 Agilus30Clear 0 TissueMatrix 0 VeroMagenta-V 0 VeroPureWhite 0.062 Agilus30Clear 0 TissueMatrix 0 VeroMagenta-V 0 VeroPureWhite 0 Agilus30Clear 0 TissueMatrix 0 VeroMagenta-V 0 VeroPureWhite 40.188 Agilus30Clear 24.96 TissueMatrix 10.583 VeroMagenta-V 10.658 VeroPureWhite 49.989 Agilus30Clear 49.728 TissueMatrix 49.728 VeroMagenta-V 49.728 Hello Anonymous
this solution should work out for you. Is dynamic as well. Condition is that all columns with "Usage" in the name are put in one column and all other as well (This logic we can also change). After the final table is create, I applied a group function to sum all the same materials
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}}), ColumnsMaterial = List.Select(Table.ColumnNames(#"Changed Type"), each not Text.Contains(_, "Usage")), ColumnsUsage = List.Select(Table.ColumnNames(#"Changed Type"), each Text.Contains(_, "Usage")), CreateListMaterial = List.Combine(List.Transform(ColumnsMaterial, (column)=> Table.Column(#"Changed Type", column))), CreateListUsage = List.Combine(List.Transform(ColumnsUsage, (column)=> Table.Column(#"Changed Type", column))), CombineFinalTable = Table.FromColumns({CreateListMaterial, CreateListUsage}, {"Material", "Usage"}), #"Grouped Rows" = Table.Group(CombineFinalTable, {"Material"}, {{"Usage Sum", each List.Sum([Usage]), type number}}) in #"Grouped Rows"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If you need any help for this, come back to me
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHi, Anonymous
Or try this:
let Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("5Zc9a8MwEIb/SvCcHrqTTpaylc6BDm06lA6GitZgGrATKJT+9yqQZDk1ibgpZPXHg/T4fc/260+zXL+nYbbsNmnsu2GGzaJZpXH9uB3Ty2e/Sc1cXDJ7nrqP1CzYQ4wkzlNG3H/0w3ay5mFI3SgRdEBYCx6jOG8z4qmfpm3Kh8b+WxLsgYABjJUEt9/HMl/0tenuVhLhDghywB5/5//K2ENOqDBFC2dEHi1QAG+4aOGMyKMGIrAkEe6syKOFCEx0QsJFiSBAlquoSoQDw6hKBEP0klCTCGyBAitl2AiGdPUgD8G1GhkB2Hidi5ysU+W4RIVz4Fnuo1IFunJHLldhyw25VEUE77WpYAtk5LyqqghnFbqhmSe3c7pYBHAclDIMEFmVCzl4KzTIm2sMGG0pCDzJh1CVBASKQZUEBFdYROWAYNTWIk/cNupqkd89KEddhYsyoOpLYkdQdwKt7q1xxZ1AsK2uEpg/H3SNKAKqCrEjqFNgjFzFjaQgb97fbAXKvxJXsfG3Pw==", BinaryEncoding.Base64),Compression.Deflate))), trsp = Table.Transpose(Table.DemoteHeaders(Source)), rmv_nm = Table.TransformColumns(trsp, {"Column1", each Text.Remove(_, {"0".."9"})}), trans = List.Transform(Table.Split(rmv_nm, 2), each Table.PromoteHeaders(Table.Transpose(_))), cmbTbls = Table.Combine(trans), grp = Table.Group(cmbTbls, {"Model Material "}, {{"Model Material Usage", each List.Sum([Model Material Usage]), type number}}) in grplet 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
