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...
Need Help
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

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

      Hi Pete

       

      thanks so much for your prompt reply.

       

      I have some more issues with the data as i went through it now  and presenting the same here 

       

      (1)the source Table has values filled in for payment mode even where the % is 0

      (2) the values in percentage column are whole numbers . how do i add the % symbol in the description

      (3) Days field has been provided for all payment modes, though the value is 0 for Mode 1 and Mode 2 for all the rows

       

      PAYMENT_MODEDAYSPERCENTAGEPAYMENT_MODE_2DAYS_2PERCENTAGE_2PAYMENT_MODE_3DAYS_3PERCENTAGE_3
      TT0100DP 0LC-S00
      TT030DP 0LC-U6070
      TT00DP 100LC-S00

       

      Kindly advise how to resolve this

       

      regards

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

        Hi GVTionale 

         

        1) Please try this:

         

         

         

        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),
    remUnusedCols = Table.RemoveColumns(mergePaymentTerms,{"Mode1", "Mode2", "Mode3", "daysDesc"}),
    pctDataTypes = Table.TransformColumnTypes(remUnusedCols,{{"Mode1 %", Percentage.Type}, {"Mode2 %", Percentage.Type}, {"Mode3 %", Percentage.Type}})
in
    pctDataTypes

         

         

         

         

        I've adjusted the replace step so it swaps zeroes for null now instead of spaces. Also removed unused columns.

         

        2) The final step in the above should set these columns to Percent data type

         

        3) The code abve should fix this providing Mode1 and mode2 days are ALWAYS zero.

         

        Pete