--Month Query
let
Source = Excel.CurrentWorkbook(){[Name="Month"]}[Content],
InsertedIndex = Table.AddIndexColumn(Source,"Index"),
ReorderedColumns = Table.ReorderColumns(InsertedIndex,{"Index", "Month"})
in
ReorderedColumns
--Contract Query
let
Source = Excel.CurrentWorkbook(){[Name="Contract"]}[Content],
InsertedIndex = Table.AddIndexColumn(Source,"Index"),
RenamedColumns = Table.RenameColumns(InsertedIndex,{{"Index", "ContractID"}}),
ReorderedColumns = Table.ReorderColumns(RenamedColumns,{"ContractID",
"Months In Contract", "Contract Amount"}),
InsertedCustom = Table.AddColumn(ReorderedColumns, "Allocated Amount",
each [Contract Amount]/[Months In Contract]),
InsertedCustom1 = Table.AddColumn(InsertedCustom, "Custom",
each Table.FirstN(Month, [Months In Contract])),
# "Expand Custom" = Table.ExpandTableColumn(InsertedCustom1, "Custom",
{"Index", "Month"}, {"Custom.Index", "Custom.Month"}),
RenamedColumns1 = Table.RenameColumns(#"Expand Custom",{{"Custom.Index", "MonthID"},
{"Custom.Month", "Month"}}),
ChangedType = Table.TransformColumnTypes(RenamedColumns1,{{"Allocated Amount", type number},
{"Contract Amount", type number}, {"MonthID", type number},
{"Months In Contract", type number}, {"ContractID", type number}})
in
ChangedType