display only those columns where fields are not empty

[GVTionale]

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?

[BA_Pete]

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]

here is the sample data -

Prof_Inv#	Customer	Pmt_Mode1	Mode1 %	Pmt_Mode2	Mode2 %	Pmt_Mode3	Mode3 %	Days
101	Adrien	TT	100%
112	Charlie	TT	30%			LC-USANCE	70%	90
123	Debbie	TT	30%	DP	70%

Expected Table output Visual (Customer Page)

Customer : Charlie

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

Hope this helps

[BA_Pete]

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]

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_MODE	DAYS	PERCENTAGE	PAYMENT_MODE_2	DAYS_2	PERCENTAGE_2	PAYMENT_MODE_3	DAYS_3	PERCENTAGE_3
TT	0	100	DP		0	LC-S	0	0
TT	0	30	DP		0	LC-U	60	70
TT	0	0	DP		100	LC-S	0	0

Kindly advise how to resolve this

regards

[BA_Pete]

Hi @GVTionale ,

Did this work out OK for you?

If so, can you mark the solution that worked please? This ensures that people are directed to the correct answer if they've got the same issue.

Thanks,

Pete

[BA_Pete]

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