Forum Discussion
Value not stable
- 10 years ago
That was the solution
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"