Forum Discussion

gauravnarchal's avatar
gauravnarchal
Post Prodigy
5 years ago
Solved

Show value in column

Hello All

 

I need your help on the below.

 

Is there a way I can show all the “charges” against each product in the Column instead of Row?

 

I have attached the PBIX sample data. Click here

 

Thanks

 

 

 

 

Need Help
Show and Tell

4 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    gauravnarchal 

    In Power Query, you need to apply some transformations to organize the data. Please check the steps in the attached file below my signature

     

     

     

    • gauravnarchal's avatar
      gauravnarchal
      Post Prodigy
      5 years ago

      Hi Fowmy   -  I have followed the steps in the file and am getting the result.

      Few fixes are required and need your help.

      • When there is no charge to a product, why that transaction is not showing in the table? Is it possible to show the charge as “0”?
      • When there are two products and one charge, both transactions should show, and the total charge should be shown only on the first transaction whereas the second transaction should show “0”.

      Thanks for your help in advance.

       

      Gaurav

      • v-jingzhang's avatar
        v-jingzhang
        Community Support
        5 years ago

        Hi gauravnarchal 

         

        I tested with Fowmy's file and add some rows according to your description.

         

        For the first fix, you will see that the Bottle record exists after transformation with the charge value as null. You can replace null with 0 if you want.

         

        For the second fix, are these two products the same product that share the same invoice number? If so, they will be aggregated into the same row. I think this is an acceptable result. If they are different products with the same invoice number or same products with different invoice numbers, I haven't tested these cases. I think the charge row must have a invoice number that is consistent with one of its above rows.

         

        If these data don't look like yours, can you share some new sample data to show your expected result to help us understand your new requirement better?

         

        Regards,
        Community Support Team _ Jing
        If this post helps, please Accept it as the solution to help other members find it.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    This M code works

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZRNb4MwDIb/C+dKsxM7Icet2nFSD71VPTAJ0UmoQ6w97N8Pd3w4rTfEgYCeJ054cQ6HImHJkIpN4YgTu3IYPXddWw/3BAmBKUUaHtBDcdw88DJ6qc7DpQX+UxhebopdXfUaD4KDiaPgH+e6ul9TzB3CyXGUbk5Xf408ogveC4/Ms+D8JLDU37afl1OuyLZdmZTCkxJkG/vrey7IPD7CIhCMggeptv/uc0G+tneqQpiFYAm/83FchESjQKUI22un8dt6YzBxGe3a6pIXCHcGQ5yyQFnta9NoXF5RueyY3bQBDjLVW11dFM/xiUVxAdlIjkEltz1VfSORu4eQSxfBRAmseGMWrzWvChZVsBaqIiWVkIWqMNdQlQvPMa6AQQdosDq6NEdngku/OdX/M2p3sr9rzRWclsZfIVmfKf+wAQjRZBmsHzIuP6T1FUYyDMeLRUr94w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [InvoiceID = _t, InvoiceDetailID = _t, Product = _t, SerialNo = _t, GrossAmount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceID", Int64.Type}, {"InvoiceDetailID", Int64.Type}, {"Product", type text}, {"SerialNo", type text}, {"GrossAmount", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"InvoiceID", Order.Ascending}, {"InvoiceDetailID", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each if [SerialNo]="Charge" then "Charge" else "Gross Amount"),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom","Charge",null,Replacer.ReplaceValue,{"SerialNo"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"SerialNo"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"InvoiceDetailID"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "GrossAmount", List.Sum)
in
    #"Pivoted Column"

    Hope this helps.