PowerQuery 填充缺失的日期和数据 [英] PowerQuery Fill missing dates and data

问题描述

如何让 PowerQuery:

How can you get PowerQuery to:

1. 生成缺失的日期

1. Generate the missing dates

用以前的最新值填充缺失的日期

Fill the missing dates with the previous most recent values

所需的输入和输出概述如下:

Desired input and output are outlined below:

输入

+------------+------+-------+
|DATE        | CUR  | RATE  |
+------------+------+-------+
| 01-01-2019 | EUR  | 0.7   |
| 01-01-2019 | USD  | 0.8   |
| 03-01-2019 | EUR  | 0.71  |
| 03-01-2019 | USD  | 0.81  |

输出

+------------+------+-------+
|DATE        | CUR  | RATE  |
+------------+------+-------+
| 01-01-2019 | EUR  | 0.7   |
| 01-01-2019 | USD  | 0.8   |
| 02-01-2019 | EUR  | 0.7   |
| 02-01-2019 | USD  | 0.8   |
| 03-01-2019 | EUR  | 0.71  |
| 03-01-2019 | USD  | 0.81  |

推荐答案

试试下面的

(a) 透视原始数据 (b) 创建从原始列表的最小值到最大值的所有日期 (c) 删除重复项 (d) 将缺失的日期附加到原始表 (e) 使用填充...向下填充 (f)转轴

(a) pivot original data (b) creates all dates from min to max of original list (c) removes duplicates (d) appends missing dates to original table (e) uses fill...down to populate (f) unpivots

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type number}, {"CUR", type text}, {"RATE", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[CUR]), "CUR", "RATE", List.Sum),
List = Table.FromList(List.Difference({List.Min(#"Changed Type"[DATE])  .. List.Max(#"Changed Type"[DATE])},#"Changed Type"[DATE]), Splitter.SplitByNothing(), null,null),
#"Renamed Columns" = Table.RenameColumns(List,{{"Column1", "DATE"}}),
Combined= #"Pivoted Column" & #"Renamed Columns",
#"Sorted Rows" = Table.Sort(Combined,{{"DATE", Order.Ascending}}),
#"Filled Down" = Table.FillDown(#"Sorted Rows",{"EUR", "USD"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"DATE", type date}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"DATE"}, "Attribute", "Value")
in #"Unpivoted Columns"

这篇关于PowerQuery 填充缺失的日期和数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！