1

in power query I have data ROE = 23,85 but when I actually trying to filter this value it says this value doesnt exist neither it can be found within merging functions.

The situation is printscreened in following: DATA Result after filtering the ROE

Everything is refreshed, uploaded...

The data source is from SQL table - can it be any problem there? (But some other numbers in the same column are showing (filtering and mergind) correctly, no pattern.

Dropdown option with multiple rows looks: Dropdown Dropdown1 Dropdown2

If M Code would help, here it is:

    let
    Source = Sql.Database("xxxxxxxxxxx", "xxxxxxx"),
    stage_onecore_RoeCalculation = Source{[Schema="stage_onecore",Item="RoeCalculation"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(stage_onecore_RoeCalculation,{"Contract No_", "Entry No_", "ROE _"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Contract No_"}, {{"MinEntryNo", each List.Min([Entry No_]), Int64.Type}, {"All", each _, type table [Contract No_=nullable text, Entry No_=nullable number, ROE _=nullable number]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Entry No_", "ROE _"}, {"Entry No_", "ROE _"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded All", each ([Entry No_] = [MinEntryNo])),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"ROE _", "ROE %"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Contract No_] = "12187012" or [Contract No_] = "15761011" or [Contract No_] = "1100000064") and ([#"ROE %"] = 23.93))
in
    #"Filtered Rows"

so i tried to do a SQL:

SELECT
  [Contract No_],
  [Entry No_],
  [ROE _]
FROM
  stage_onecore.RoeCalculation
WHERE
  [Entry No_] IN (
    SELECT
      MIN([Entry No_])
    FROM
      stage_onecore.RoeCalculation
    GROUP BY
      [Contract No_]
  )

and if iam doing it from power bi or excel it doesnt work without any other changes/steps... It seems if I would group it it works fine but after the grouping some numbers doesnt work...

Dominik
  • 23
  • 5
  • Try using the drop down arrow at the top right of ROE % column to apply the filter by placing an [x] next to the value you are filtering for. See what code is generated. You can also edit your question to paste in your code that you obtain from home...advanced editor... for additional help – horseyride Mar 15 '23 at 14:02
  • Thanks for trying to help. I am enclosing the dropdown option with printscreens - also doesnt work. – Dominik Mar 15 '23 at 14:11
  • why are you filtering on contract number? What happens if you just filter ROE? – horseyride Mar 15 '23 at 14:18
  • some numbers are showing correctly therefore Iam looking at another table where I see which numbers doesnt match with merging and then I just filter the examples with the contract no, those no cant be seen while filtering nor the merging – Dominik Mar 15 '23 at 14:21
  • if the data is properly refreshed and you use the dropdown to filter, there should be no reason that row does not show up. To make sure the data is refreshed you could copy the code into a second query and see if it works there – horseyride Mar 15 '23 at 14:29
  • Well I dont see any reason neither. Copying to a different query results in same outcome. – Dominik Mar 15 '23 at 15:23
  • Sorry, I have no further ideas. Perhaps someone else can help – horseyride Mar 15 '23 at 15:59
  • Try with 23,93 instead of 23.93 – Ashok Anumula Mar 16 '23 at 10:21
  • Tried that as well. :) Thank you but that also doesnt work. – Dominik Mar 16 '23 at 11:21
  • 1
    how about and (Number.IntegerDivide([#"ROE %"]*100, 1) = 2393)) – horseyride Mar 16 '23 at 11:52
  • I cannot reproduce your problem with the values you show in your screenshot. For better assistance I suggest you post something that will allow us to reproduce your problem. M-Code as text that **reproduces** the problem would be most helpful. Your posted M-Code does not include the data, so is not really helpful. – Ron Rosenfeld Mar 16 '23 at 12:13
  • I think its impossible to reproduce the problem... Those are normal numbers... – Dominik Mar 16 '23 at 13:36
  • If you can't reproduce the problem, then just re-do your query and it will be fine. – Ron Rosenfeld Mar 16 '23 at 13:38
  • creating calculated column works with those numbers and also the new column can be filtered but this is not normal :D – Dominik Mar 16 '23 at 13:39
  • I tried to copy this query to excel and it is the same. Can it be some corrupted data from source SQL table? – Dominik Mar 16 '23 at 13:40
  • Without my being able to reproduce your problem here, it is not possible for me to comment on the cause. – Ron Rosenfeld Mar 16 '23 at 14:29
  • so i tried to do a SQL: SELECT [Contract No_], [Entry No_], [ROE _] FROM stage_onecore.RoeCalculation WHERE [Entry No_] IN ( SELECT MIN([Entry No_]) FROM stage_onecore.RoeCalculation GROUP BY [Contract No_] ) and if iam doing it from power bi or excel it doesnt work without any other changes/steps... It seems if I would group it it works fine but after the grouping some numbers doesnt work... – Dominik Mar 16 '23 at 14:50

0 Answers0