Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
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.
Proud to be a Datanaut!
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
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
Proud to be a Datanaut!
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
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
97 | |
93 | |
87 | |
68 |
User | Count |
---|---|
173 | |
134 | |
132 | |
102 | |
95 |