HI All ,
can anyone help me to fix the Geo Code ? i'd like to put this on Map , and have no idea why it doesn't work. I tried with multiple ways , by seperate Long and Latt , and another way by combining together with "," . Look like Geo Code doesn't work Well on Map. or it is too much Data ?
Below is my MCode
let
Source = Excel.Workbook(File.Contents("C:\Users\hchanleakna\Desktop\Power BI\32- Survey for Non-Sale Team\MIT_01-MIT_28.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Unique ID", type text}, {"No Label", type text}, {"Surveyor Name / ឈ្មោះអ្នកធ្វើការស្ទង់មតិ", type text}, {"Pop Up Outlet Gold/Silver", type text}, {"Gold / Silver មាស / ប្រាក់", type text}, {"On / Off Premise", type text}, {"Cooler / Non Cooler : មានទូរ / មិនមានទូរ", type text}, {"Channel / ដេប៉ូ", type text}, {"IP", type text}, {"Comments / ផ្តល់មតិយោបល់ផ្សេងៗ", type text}, {"Attribute.2", type text}, {"Category", type text}, {"Value", type text}, {"latt", type number}, {"long", type number}, {"Qestion ", type text}, {"Customer Code", Int64.Type}, {"Customer Name", type text}, {"Date", type date}, {"Condition", type text}, {"Off/On , Segmentation", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Pop Up Outlet Gold/Silver", Splitter.SplitTextByEachDelimiter({"#(tab)"}, QuoteStyle.Csv, false), {"Pop Up Outlet Gold/Silver.1", "Pop Up Outlet Gold/Silver.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Pop Up Outlet Gold/Silver.1", Int64.Type}, {"Pop Up Outlet Gold/Silver.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Pop Up Outlet Gold/Silver.1", "Customer 1"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Pop Up Outlet Gold/Silver.2"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Unique ID", "No Label", "Surveyor Name / ឈ្មោះអ្នកធ្វើការស្ទង់មតិ", "Customer Code", "Customer Name", "Customer 1", "Gold / Silver មាស / ប្រាក់", "On / Off Premise", "Cooler / Non Cooler : មានទូរ / មិនមានទូរ", "Channel / ដេប៉ូ", "IP", "Comments / ផ្តល់មតិយោបល់ផ្សេងៗ", "Attribute.2", "Category", "Value", "latt", "long", "Qestion ", "Date", "Condition", "Off/On , Segmentation"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Customer 1"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns1",{"Date", "Unique ID", "No Label", "Surveyor Name / ឈ្មោះអ្នកធ្វើការស្ទង់មតិ", "Customer Code", "Customer Name", "Gold / Silver មាស / ប្រាក់", "On / Off Premise", "Cooler / Non Cooler : មានទូរ / មិនមានទូរ", "Channel / ដេប៉ូ", "IP", "Comments / ផ្តល់មតិយោបល់ផ្សេងៗ", "Attribute.2", "Category", "Value", "latt", "long", "Qestion ", "Condition", "Off/On , Segmentation"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"On / Off Premise", "Channel"}}),
#"Reordered Columns2" = Table.ReorderColumns(#"Renamed Columns1",{"Date", "Unique ID", "No Label", "Surveyor Name / ឈ្មោះអ្នកធ្វើការស្ទង់មតិ", "Customer Code", "Customer Name", "Channel", "Channel / ដេប៉ូ", "Gold / Silver មាស / ប្រាក់", "Cooler / Non Cooler : មានទូរ / មិនមានទូរ", "IP", "Comments / ផ្តល់មតិយោបល់ផ្សេងៗ", "Attribute.2", "Category", "Value", "latt", "long", "Qestion ", "Condition", "Off/On , Segmentation"}),
#"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns2",{{"Channel / ដេប៉ូ", "Trade"}, {"Gold / Silver មាស / ប្រាក់", "Segmensation"}, {"Cooler / Non Cooler : មានទូរ / មិនមានទូរ", "Cooler or Non"}}),
#"Reordered Columns3" = Table.ReorderColumns(#"Renamed Columns2",{"Date", "Unique ID", "No Label", "Surveyor Name / ឈ្មោះអ្នកធ្វើការស្ទង់មតិ", "Customer Code", "Customer Name", "Channel", "Trade", "Segmensation", "Cooler or Non", "Comments / ផ្តល់មតិយោបល់ផ្សេងៗ", "IP", "Attribute.2", "Category", "Value", "latt", "long", "Qestion ", "Condition", "Off/On , Segmentation"}),
#"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns3",{"IP", "Condition"}),
#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns2",{{"Off/On , Segmentation", "Custom"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns3", each ([Unique ID] <> "MIT_1")),
#"Removed Columns3" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}),
#"Renamed Columns4" = Table.RenameColumns(#"Removed Columns3",{{"Qestion ", "Question"}}),
#"Appended Query" = Table.Combine({#"Renamed Columns4", #"02nd Phase"}),
#"Replaced Value" = Table.ReplaceValue(#"Appended Query","No have cooler","No Cooler",Replacer.ReplaceText,{"Cooler or Non"}),
#"Filtered Rows1" = Table.SelectRows(#"Replaced Value", each ([Cooler or Non] <> "") and ([Value] <> "")),
#"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows1","No have","No Rack",Replacer.ReplaceText,{"Value"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","No have rack","No Rack",Replacer.ReplaceText,{"Value"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","No Plastic Rack","No Rack",Replacer.ReplaceText,{"Value"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","No Plastic racks","No Rack",Replacer.ReplaceText,{"Value"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","មិនមាន","No Rack",Replacer.ReplaceText,{"Value"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Other","No Rack",Replacer.ReplaceText,{"Value"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","No plastic rack","No Rack",Replacer.ReplaceText,{"Value"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","No Plastic racks","No Rack",Replacer.ReplaceText,{"Value"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","No Rack rack","No Rack",Replacer.ReplaceText,{"Value"}),
#"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","No Racks","No Rack",Replacer.ReplaceText,{"Value"}),
#"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","NA","No Rack",Replacer.ReplaceText,{"Value"}),
#"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11","cooler return","",Replacer.ReplaceText,{"Value"}),
#"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12","cooler returned","",Replacer.ReplaceText,{"Value"}),
#"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13","cooler rturn","",Replacer.ReplaceText,{"Value"}),
#"Replaced Value15" = Table.ReplaceValue(#"Replaced Value14","no cooler","",Replacer.ReplaceText,{"Value"}),
#"Replaced Value16" = Table.ReplaceValue(#"Replaced Value15","No Rack","",Replacer.ReplaceText,{"Value"}),
#"Replaced Value17" = Table.ReplaceValue(#"Replaced Value16","No need","NO / មិនត្រូវការ",Replacer.ReplaceText,{"Value"}),
#"Filtered Rows2" = Table.SelectRows(#"Replaced Value17", each ([Value] <> "")),
#"Replaced Value18" = Table.ReplaceValue(#"Filtered Rows2","No cooler","",Replacer.ReplaceText,{"Value"}),
#"Filtered Rows3" = Table.SelectRows(#"Replaced Value18", each ([Value] <> "")),
#"Replaced Value19" = Table.ReplaceValue(#"Filtered Rows3","ed","",Replacer.ReplaceText,{"Value"}),
#"Filtered Rows4" = Table.SelectRows(#"Replaced Value19", each ([Value] <> "")),
#"Renamed Columns5" = Table.RenameColumns(#"Filtered Rows4",{{"Value", "Answers"}}),
#"Replaced Value20" = Table.ReplaceValue(#"Renamed Columns5","Racks","Rack",Replacer.ReplaceText,{"Attribute.2"}),
#"Replaced Value21" = Table.ReplaceValue(#"Replaced Value20","Plastic,Racks","Plastic,Rack",Replacer.ReplaceText,{"Question"}),
#"Renamed Columns6" = Table.RenameColumns(#"Replaced Value21",{{"Cooler or Non", "Coolers"}}),
#"Filtered Rows5" = Table.SelectRows(#"Renamed Columns6", each ([Date] <> #date(2019, 1, 24))),
#"Renamed Columns7" = Table.RenameColumns(#"Filtered Rows5",{{"latt", "Latt"}, {"long", "Long"}}),
#"Appended Query1" = Table.Combine({#"Renamed Columns7", #"02nd Phase (2)"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Appended Query1",{{"Customer Code", type text}}),
#"Filtered Rows6" = Table.SelectRows(#"Changed Type2", each ([Answers] <> "")),
#"Replaced Value22" = Table.ReplaceValue(#"Filtered Rows6","មិនមានទាំងអស់ / NO ALL between Plastic Racks and Bi Posters","NO ALL",Replacer.ReplaceText,{"Answers"}),
#"Replaced Value23" = Table.ReplaceValue(#"Replaced Value22","មិនមានទាំងអស់ / NO ALL either Plastic Racks and Bi Posters ","NO ALL ",Replacer.ReplaceText,{"Answers"}),
#"Replaced Value24" = Table.ReplaceValue(#"Replaced Value23","ក្លាហានធ្នើរ","Klahan Bi-Poster",Replacer.ReplaceText,{"Answers"}),
#"Replaced Value25" = Table.ReplaceValue(#"Replaced Value24","ក្លាហាន តាំងផលិតផល","Klahan Plastic Rack",Replacer.ReplaceText,{"Answers"}),
#"Replaced Value26" = Table.ReplaceValue(#"Replaced Value25","ប្រយុទ្ធតាំង ផលិតផល ","Broyuth Bi-Poster",Replacer.ReplaceText,{"Answers"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value26",{{"Long", type text}, {"Latt", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type3", "Custom", each Text.Combine({[Latt],",",[Long]})),
#"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"Custom", Text.Trim, type text}})
in
#"Trimmed Text"
