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

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 1Model Material 1 UsageModel Material 2Model Material 2 UsageModel Material 3Model Material 3 UsageModel Material 4Model Material 4 Usage
VeroPureWhite56.992Agilus30Clear33.619TissueMatrix18.039VeroMagenta-V24.561
VeroMagenta0VeroPureWhite28.605Agilus30Clear22.325TissueMatrix9.522
VeroPureWhite52.155Agilus30Clear34.051TissueMatrix15.961VeroMagenta-V17.285
VeroPureWhite39.022Agilus30Clear26.847TissueMatrix8.506VeroMagenta-V12.31
VeroPureWhite44.657Agilus30Clear26.145TissueMatrix8.535VeroMagenta-V9.665
VeroPureWhite53.209Agilus30Clear35.149TissueMatrix16.944VeroMagenta-V18.458
VeroPureWhite0.223Agilus30Clear0TissueMatrix0VeroMagenta-V0
VeroPureWhite42.624Agilus30Clear31.298TissueMatrix11.424VeroMagenta-V12.515
VeroPureWhite17.799Agilus30Clear2.116TissueMatrix2.116VeroMagenta-V2.116
VeroPureWhite0.132Agilus30Clear0TissueMatrix0VeroMagenta-V0
VeroPureWhite1.374Agilus30Clear1.058TissueMatrix1.058VeroMagenta-V1.058
VeroPureWhite0.008Agilus30Clear0TissueMatrix0VeroMagenta-V0
VeroPureWhite0.062Agilus30Clear0TissueMatrix0VeroMagenta-V0
VeroPureWhite0Agilus30Clear0TissueMatrix0VeroMagenta-V0
VeroPureWhite40.188Agilus30Clear24.96TissueMatrix10.583VeroMagenta-V10.658
VeroPureWhite49.989Agilus30Clear49.728TissueMatrix49.728VeroMagenta-V49.728
ziying35's avatar
ziying35
Impactful Individual
5 years ago

Hi, 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
    grp
  • 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

      • ziying35's avatar
        ziying35
        Impactful Individual
        5 years ago

        Hi, Anonymous 

         

        The type table expression in Power Query doesn't really set the data to a particular type, and even if it could, it wouldn't be easy to write as a dynamic expression.