How can I verify if a column is only decimal using Power Query M formula

0 votes

Power Query is new to me. Based on a search criterion for column 3, and assuming that those columns include only decimal values, I need to replace the value of column 4 with only decimal values in columns 5 and 6. The data file sample is as follows:

enter image description here

I tried to use Number.Mod , Value.Type and Value.Is functions but that did not work. Here is the code for my latest attempt:

    = Table.ReplaceValue(#"Changed Type",each [Column4],
  each if Text.Contains([Column3],"Instant Payment BSF/MBK") then 
          if Number.Mod([Column5], 1) = 0 then [Column5] 
          else if Number.Mod([Column6], 1) = 0 then [Column6]
          else [Column4]              
  else [Column4],Replacer.ReplaceValue,{"Column4"})
Feb 13, 2023 in Others by Kithuzzz
• 38,020 points
853 views

1 answer to this question.

0 votes

Try this:

= Table.ReplaceValue(#"Changed Type",each [Column4],
  each if Text.Contains([Column3],"Instant Payment") then 
          if Value.Is([Column4], type number) or Value.Is(Value.FromText([Column4]), type number) then [Column4]
          else if Value.Is([Column5], type number) or Value.Is(Value.FromText([Column5]), type number) then [Column5]
          else if Value.Is([Column6], type number) or Value.Is(Value.FromText([Column6]), type number) then [Column6]
          else [Column4]              
  else [Column4],Replacer.ReplaceValue,{"Column4"})
answered Feb 13, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
0 answers

how can I deploy a test in pytest framework to airflow using DAG?

I have established an automative testing framework ...READ MORE

Oct 4, 2021 in Others by Yuan
• 120 points
634 views
0 votes
1 answer

Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE

answered Nov 11, 2022 in Others by narikkadan
• 63,720 points
1,338 views
0 votes
1 answer

How can i add cells if condition is met

Both the AND function and nested if ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,720 points
712 views
0 votes
1 answer

How can I store the data of an open excel workbook in a collection using BluePrism?

To do what you want is like ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,720 points
1,255 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
4,169 views
0 votes
1 answer

Power Query : http POST request with form data

Try using Uri.BuildQueryString and Json.Document. let ...READ MORE

answered Dec 12, 2018 in Power BI by Upasana
• 8,620 points
8,644 views
0 votes
1 answer

Power Query : Adding Columns and Multiple files

The following call, FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) shall replace the ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
1,641 views
0 votes
1 answer

Power Query : making HTTP POST request with form data

Try Using Uri.BuildQueryString and Json.Document let ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
6,011 views
0 votes
1 answer

How can I add a wildcard to my if formula (that also contains an or condition)

3 Try: The formula in B1: =IF(SUM(COUNTIF(A1,{"* IDE","* IDE-?"})),"Y","N") Or, a little ...READ MORE

answered Feb 2, 2023 in Others by narikkadan
• 63,720 points
537 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP