Forum Discussion
Value not stable
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
That was the solution
7 Replies
Can you post your formula for overall rank?
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"
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?
