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
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