Forum Discussion
Helper IIdisplay only those columns where fields are not empty
Helper IIhere 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
Super UserHi 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
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 pctDataTypesI'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