Value not stable

[GD61]

Hi,

I'm trying to do the following example:

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-getting-started/

In the data base, Nebraska is rankig at #10

But in every report I create, Nebraska ranking displays 20. So I have 2 states ranking at 20 : Nebraska and Pennsylvania.

What did I miss? Thanks

[Greg_Deckler]

I downloaded the data from the example and created a simple table like yours and I see Nebraska as 10 in rank. What is interesting is that all of my numbers match yours except for Nebraska and both of the Nebraska numbers that I have are half of yours so it appears as if you have somehow doubled Nebraska's numbers but not sure how you did that, perhaps somewhere in your query when you were bringing in the data?

[GD61]

Hello,

I found that Nebraska appears twice in my data base.Do you find the same issue ? 

[Greg_Deckler]

Nope, only appears once in mine. That would explain it though, the default aggregation is SUM so it is adding together both of your entries, hence why your numbers for Nebraska are twice as high as mine.

[GD61]

I noticed that in the original database the name Nebraska appears tow times. One with the abbreviation, the other without.

I'll need to eliminate several rows at the bottom :

[GD61 · Accepted solution]

That was the solution

[Greg_Deckler]

Can you post your formula for overall rank?

[GD61]

Is it his formula you need?

-------------------------------------

let
Source = Web.Page(Web.Contents("http://www.bankrate.com/finance/retirement/best-places-retire-how-state-ranks.aspx")),
Data0 = Source{0}[Data],
#"Type modifié" = Table.TransformColumnTypes(Data0,{{"Header", type text}, {"Overall rank", Int64.Type}, {"State", type text}, {"Cost of living", Int64.Type}, {"Crime rate", Int64.Type}, {"Community well-being", Int64.Type}, {"Health care quality", type text}, {"Tax rate", Int64.Type}, {"Weather", Int64.Type}}),
#"Colonnes supprimées" = Table.RemoveColumns(#"Type modifié",{"Header"}),
#"Valeur remplacée" = Table.ReplaceValue(#"Colonnes supprimées","38 (tie)","38",Replacer.ReplaceText,{"Health care quality"}),
#"Valeur remplacée1" = Table.ReplaceValue(#"Valeur remplacée","35 (tie)","35",Replacer.ReplaceText,{"Health care quality"}),
#"Valeur remplacée2" = Table.ReplaceValue(#"Valeur remplacée1","45 (tie)","45",Replacer.ReplaceText,{"Health care quality"}),
#"Valeur remplacée3" = Table.ReplaceValue(#"Valeur remplacée2","27 (tied)","27",Replacer.ReplaceText,{"Health care quality"}),
#"Type modifié1" = Table.TransformColumnTypes(#"Valeur remplacée3",{{"Health care quality", Int64.Type}}),
#"Requêtes fusionnées" = Table.NestedJoin(#"Type modifié1",{"State"},US_States,{"State Name"},"NewColumn",JoinKind.LeftOuter),
#"NewColumn développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "NewColumn", {"State Name", "State Code"}, {"NewColumn.State Name", "NewColumn.State Code"}),
#"Colonnes supprimées1" = Table.RemoveColumns(#"NewColumn développé",{"NewColumn.State Name"}),
#"Colonnes renommées" = Table.RenameColumns(#"Colonnes supprimées1",{{"NewColumn.State Code", "State Code"}})
in
#"Colonnes renommées"