Forum Discussion

GVTionale's avatar
GVTionale
Icon for Helper II rankHelper II
6 years ago

display only those columns where fields are not empty

I have a table containing payment terms of customer orders as follows -

 

Payment_Mode1 , Percentage of Mode1, Payment Mode2, % of Mode2 and Payment Mode3, % of Mode 3.

 

Payment Mode 1 will have value if there are terms of "TT", Mode2 for "DP" and Mode 3 for "LC" with appropriate % against each

 

Eg. IF the terms are 30% TT and 70% LC, Mode1 will have value of "TT", % will be 30, Mode 2 and % will be Blank and Mode3 and % will be LC,70%.

 

I would like to display the payment terms table on  customer orde details page and it should be only for those where the values are not blank. 

How do i do it?

Need Help

6 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    6 years ago

    Hi GVTionale 

     

    Can you provide an example of what your source data looks like, and also an example of what your desired output looks like please?

     

    Remember to remove any confidential information.

    • GVTionale's avatar
      GVTionale
      Icon for Helper II rankHelper II
      6 years ago

      here is the sample data -

      Prof_Inv#CustomerPmt_Mode1Mode1 %Pmt_Mode2Mode2 %Pmt_Mode3Mode3 %Days
      101AdrienTT100%     
      112CharlieTT30%  LC-USANCE70%90
      123DebbieTT30%DP70%   

       

      Expected Table output Visual (Customer Page)

       

      Customer : Charlie

       

      Prof_Inv#Payment Terms
      112TT -30% ; LC-USANCE 90Days - 70%

       

       

      Hope this helps

       

      • BA_Pete's avatar
        BA_Pete
        Icon for Super User rankSuper User
        6 years ago

        Hi GVTionale 

         

        I've created the [Payment Terms] field in Power Query that you can use in your report as follows:

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRckwpykzNAzJCQoCEoYGBKpBSwMCxOkANhkZAtnNGYlFOZipMhzGKBh9n3dBgRz9nVyDbHCxjaQDRa2QM5LikJiWha3UJgKtFsi4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Prof_Inv#" = _t, Customer = _t, Pmt_Mode1 = _t, #"Mode1 %" = _t, Pmt_Mode2 = _t, #"Mode2 %" = _t, Pmt_Mode3 = _t, #"Mode3 %" = _t, Days = _t]),
    replacedSpaceforNull = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Mode1 %", "Pmt_Mode2", "Mode2 %", "Pmt_Mode3", "Mode3 %", "Days"}),
    mergeMode1 = Table.AddColumn(replacedSpaceforNull, "Mode1", each Text.Combine({[Pmt_Mode1], [#"Mode1 %"]}, " - "), type text),
    mergeMode2 = Table.AddColumn(mergeMode1, "Mode2", each Text.Combine({[Pmt_Mode2], [#"Mode2 %"]}, " - "), type text),
    mergeMode3 = Table.AddColumn(mergeMode2, "Mode3", each Text.Combine({[Pmt_Mode3], [#"Mode3 %"]}, " - "), type text),
    mergeDaysDesc = Table.AddColumn(mergeMode3, "daysDesc", each if [Days] <> null then Text.Combine({[Days], "days"}, " ") else null, type text),
    mergePaymentTerms = Table.AddColumn(mergeDaysDesc, "Payment Terms", each Text.Combine(List.Select({[Mode1],[Mode2],[Mode3], [daysDesc]}, each _ <> "" and _ <> null), " ; "), type text),
    pctDataTypes = Table.TransformColumnTypes(mergePaymentTerms,{{"Mode1 %", Percentage.Type}, {"Mode2 %", Percentage.Type}, {"Mode3 %", Percentage.Type}})
in
    pctDataTypes

         

         

        Paste this into a blank query using Advanced Editor so you can follow my steps.

         

        I get the following output which can, of course, be filtered as you require:

         

        Pete