Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
GVTionale
Helper II
Helper II

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

6 REPLIES 6
BA_Pete
Super User
Super User

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.



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

 

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:

GVTionale.PNG

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.