#excel #dax #powerquery #powerpivot
#excel #dax #powerquery #powerpivot
Вопрос:
У меня есть файл с 50 000 строками данных в 3 столбцах — уникальный идентификатор, дата начала и дата окончания.
Используя Power Pivot, мне нужно определить, имеют ли какие-либо записи с одинаковым уникальным идентификатором какие-либо перекрывающиеся даты. Каждый уникальный идентификатор появляется примерно 5 раз.
В Excel я бы использовал формулу
SUMPRODUCT: =SUMPRODUCT(($B3<=$C$3:$C$13)*($C3>=$B$3:$B$13)*($A$3:$A$13=A3))>1
Хотя эта формула действительно хорошо работает в Excel с более чем 50 тысячами записей, это ломает мой компьютер.
Мне было интересно, как бы я выполнил тот же расчет в сводной таблице мощности / запросе.
Большое вам спасибо!
Ответ №1:
следуя M-коду PowerQuery, это решит вашу проблему. Не знаю, сколько времени потребуется для 50 тыс. строк:
let
Quelle = Excel.CurrentWorkbook(){[Name="tab_Dates"]}[Content],
Change_Type = Table.TransformColumnTypes(Quelle,{{"Unique ID", type text}, {"Start Date", type date}, {"End Date", type date}}),
add_List_Dates = Table.AddColumn(Change_Type, "List_Dates", each List.Dates([Start Date], Duration.Days([End Date]-[Start Date]) 1 , #duration(1,0,0,0))),
expand_List_Dates = Table.ExpandListColumn(add_List_Dates, "List_Dates"),
add_CountIF_ID_Date = Table.AddColumn(expand_List_Dates, "CountIF_ID_Date", (CountRows) =>
Table.RowCount(
Table.SelectRows(
expand_List_Dates,
each
([Unique ID] = CountRows[Unique ID] and [List_Dates] = CountRows[List_Dates])))),
Change_Type_2 = Table.TransformColumnTypes(add_CountIF_ID_Date,{{"CountIF_ID_Date", type text}}),
ChangeValue_CountIF_ID_Date = Table.ReplaceValue(Change_Type_2, each [CountIF_ID_Date], each if [CountIF_ID_Date] <> "1" then "FALSE" else "TRUE",Replacer.ReplaceText,{"CountIF_ID_Date"}),
Remove_Column_List_Dates = Table.RemoveColumns(ChangeValue_CountIF_ID_Date,{"List_Dates"}),
Remove_Duplicates = Table.Distinct(Remove_Column_List_Dates)
in
Remove_Duplicates