Power Bi之人口遷徙Dashboard

偶然看到國外的一份excel文件,決定用power bi來完成.先看一下效果.

嗯.效果還不錯,但是excel的源文件不適合放進power bi里.我們先清洗一下數據.

有三份數據源,代表2010,2011,2012年.要將三份文件最終合併成一份,並不難,唯一的難點就是在數據源里,箭頭代表不同的方向,一個箭頭代表多少人到達目的城市,另一個箭頭代表離開多少人,從出發地.這增長加了一點清洗難度.

這是載入進Power bi清洗的過程.過程太多,就不介紹了......我一份源文件,自己動手清洗.如果有興趣的話.鏈接: https://pan.baidu.com/s/1bpLH1D1 密碼: uuff

代碼如下:

letn #"2010到達" = Excel.Workbook(File.Contents("C:UsersAdministratorDesktopTableau2美國地圖數據源.XLSM"), null, true){[Item="2010",Kind="Sheet"]}[Data],n 已添加自定義 = Table.AddColumn(#"2010到達", "年份", each 2010),n #"2011到達" = Excel.Workbook(File.Contents("C:UsersAdministratorDesktopTableau2美國地圖數據源.XLSM"), null, true){[Item="2011",Kind="Sheet"]}[Data],n 已添加自定義1 = Table.AddColumn(#"2011到達", "年份", each 2011),n #"2012到達" = Excel.Workbook(File.Contents("C:UsersAdministratorDesktopTableau2美國地圖數據源.XLSM"), null, true){[Item="2012",Kind="Sheet"]}[Data],n 已添加自定義2 = Table.AddColumn(#"2012到達", "年份", each 2012),n 追加的查詢 = Table.Combine({已添加自定義2, 已添加自定義1,已添加自定義 }),n 提升的標題 = Table.PromoteHeaders(追加的查詢, [PromoteAllScalars=true]),n 篩選的行 = Table.SelectRows(提升的標題, each ([Alabama] <> "Alabama")),n 逆透視的其他列 = Table.UnpivotOtherColumns(篩選的行, {"Current residence in --","2012"}, "屬性", "值"),n 到達匯總 = Table.SelectRows(Table.AddColumn(逆透視的其他列, "狀態", each "Arriving"), each ([值] <> "N/A" and [#"Current residence in --"] <> "Puerto Rico" and [#"Current residence in --"] <> "United States")),n 重命名的列1 = Table.RenameColumns(到達匯總,{{"2012", "年份"}}),n #"2010離開" = Excel.Workbook(File.Contents("C:UsersAdministratorDesktopTableau2美國地圖數據源.XLSM"), null, true){[Item="2010",Kind="Sheet"]}[Data],n 篩選的行1 = Table.SelectRows(#"2010離開", each ([Column1] <> "Puerto Rico" and [Column1] <> "United States")),n 轉置表 = Table.Transpose(篩選的行1),n #"2010年離開" = Table.PromoteHeaders(轉置表, [PromoteAllScalars=true]),n 已添加自定義3 = Table.AddColumn(#"2010年離開", "年份", each 2010),n #"2011離開" = Excel.Workbook(File.Contents("C:UsersAdministratorDesktopTableau2美國地圖數據源.XLSM"), null, true){[Item="2011",Kind="Sheet"]}[Data],n 篩選的行2 = Table.SelectRows(#"2011離開", each ([Column1] <> "Puerto Rico" and [Column1] <> "United States")),n 轉置表1 = Table.Transpose(篩選的行2),n #"2011年離開" = Table.PromoteHeaders(轉置表1, [PromoteAllScalars=true]),n 已添加自定義4 = Table.AddColumn(#"2011年離開", "年份", each 2011),n #"2012離開" = Excel.Workbook(File.Contents("C:UsersAdministratorDesktopTableau2美國地圖數據源.XLSM"), null, true){[Item="2012",Kind="Sheet"]}[Data],n 篩選的行3 = Table.SelectRows(#"2012離開", each ([Column1] <> "Puerto Rico" and [Column1] <> "United States")),n 轉置表2 = Table.Transpose(篩選的行3),n #"2012年離開" = Table.PromoteHeaders(轉置表2, [PromoteAllScalars=true]),n 已添加自定義5 = Table.AddColumn(#"2012年離開", "年份", each 2012),n 追加查詢 = Table.Combine({已添加自定義5, 已添加自定義4,已添加自定義3 }),n 逆透視的其他列1 = Table.UnpivotOtherColumns(追加查詢, {"Current residence in --","年份"}, "屬性", "值"),n 篩選的行4 = Table.SelectRows(逆透視的其他列1, each ([值] <> "N/A")),n 離開匯總 = Table.AddColumn(篩選的行4, "狀態", each "Leaving"),n 自定義1 = Table.Combine({#"離開匯總", 重命名的列1}),n 更改的類型 = Table.TransformColumnTypes(自定義1,{{"狀態", type text}, {"年份", Int64.Type},{"值", Int64.Type} , {"Current residence in --", type text}}),n 重命名的列 = Table.RenameColumns(更改的類型,{{"Current residence in --", "城市A"}, {"屬性", "城市B"}, {"值", "人口"}})ninn 重命名的列n

把城市名字的數據源也載入進去,代碼如下

letn 源 = Excel.Workbook(File.Contents("C:UsersAdministratorDesktopTableau2美國地圖數據源.XLSM"), null, true){[Item="城市",Kind="Sheet"]}[Data],n 提升的標題 = Table.PromoteHeaders(源, [PromoteAllScalars=true]),n 更改的類型 = Table.TransformColumnTypes(提升的標題,{{"州省", type text}, {"City", type text}, {"簡稱", type text}})ninn 更改的類型n

將兩個表設置如下關係

由於只要顯示TOP前5名的出發城市,離開城市人口最多的的條形圖,用人口遷徙里構造度量公式,很難做出來.會導致最後來的條形圖不完美,因為我們可以構造兩個表,一個叫離開表,一個離開表,

公式如下:

離開表 = UNION(nTOPN(5,FILTER(人口遷徙,人口遷徙[狀態]="Leaving"&&人口遷徙[年份]=2010),人口遷徙[人口],DESC),nTOPN(5,FILTER(人口遷徙,人口遷徙[狀態]="Leaving"&&人口遷徙[年份]=2011),人口遷徙[人口],DESC),nTOPN(5,FILTER(人口遷徙,人口遷徙[狀態]="Leaving"&&人口遷徙[年份]=2012),人口遷徙[人口],DESC))n

再做一份到達表.

到達表 = UNION(nTOPN(5,FILTER(人口遷徙,人口遷徙[狀態]="Arriving"&&人口遷徙[年份]=2010),人口遷徙[人口],DESC),nTOPN(5,FILTER(人口遷徙,人口遷徙[狀態]="Arriving"&&人口遷徙[年份]=2011),人口遷徙[人口],DESC),nTOPN(5,FILTER(人口遷徙,人口遷徙[狀態]="Arriving"&&人口遷徙[年份]=2012),人口遷徙[人口],DESC))n

隨便做一張時間表.公式如下,這裡用DAX完成的,其實也可以用M語言來完成.

年份表 = UNION(nROWt("年份",2010),nROWt("年份",2011),nROWt("年份",2012))n

然後設置如下關係:

在人口遷徙里編寫如下表達式:表示到達多少人口與出發了多少人口的計算.這兩個公式主要是用做散點圖.X軸離開多少人,Y軸到達多少人兩個軸.不然無法做出這樣的散點圖.

Arriving = MINX(ADDCOLUMNS (n VALUES ( 人口遷徙[城市A] ),n "DD", CALCULATE ( SUM ( 人口遷徙[人口] ), FILTER ( 人口遷徙, 人口遷徙[狀態] = "Arriving" )n)),[DD])n

離開人口的公式:

Leaving = MINX(ADDCOLUMNS (n VALUES ( 人口遷徙[城市A] ),n "LK", CALCULATE ( SUM ( 人口遷徙[人口] ), FILTER ( 人口遷徙, 人口遷徙[狀態] = "Leaving" )n)),[LK])n

然後選擇合適的欄位與圖表,進行排版就可以啦.

EasyCharts團隊出品

帥的人都關注了EasyCharts團隊^..^~

QQ交流群：553270834

微信公眾號：EasyCharts