Получаем код всех запросов из всех файлов из папки
#АнатомияФункций – custom
Всем привет!
На текущий момент на канале есть fxUnzip, есть fxGetMCode и есть пост про Folder.Files.
Мне казалось, что этого достаточно, чтобы самостоятельно собрать адекватное подключение к папке и получить код запросов из всех файлов внутри неё. Но оказалось, что я просто пишу нерабочий код и всё вышеизложенное «не взлетает». ОК, напишем код целиком:
где
func – собственно функция для вынимания кода запросов (fxUnzip интегрирована внутрь «во избежание»)
from – подключение к папке (увы, я заранее не знаю путь к конкретной папке – его придётся исправить на свой, ручками – если даже это станет проблемой – мои полномочия всё)
filtr, rem – оставили только эксельки и только нужные столбцы
tr,to – преобразовали бинарки файлов в таблицы и развернули их
И всё. Кто найдёт в коде выше File.Contents, без которой якобы не работает – пусть первым бросит в меня камень. Большая просьба думать перед тем, как писать, что что-то «не работает».
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#АнатомияФункций – custom
Всем привет!
На текущий момент на канале есть fxUnzip, есть fxGetMCode и есть пост про Folder.Files.
Мне казалось, что этого достаточно, чтобы самостоятельно собрать адекватное подключение к папке и получить код запросов из всех файлов внутри неё. Но оказалось, что я просто пишу нерабочий код и всё вышеизложенное «не взлетает». ОК, напишем код целиком:
let
lst={"QueryName","Value"},
//функция
func=(file)=>[
u16 =BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),
u32 =BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32,ByteOrder.LittleEndian),
f=(x)=>[a=Text.Split(x," = "),b={Text.Trim(Text.Replace(a{0},"shared","")),Text.Combine(List.Skip(a)," = ")}][b],
fxUnzip=(ZIP)=>
[ get=(x)=>BinaryFormat.Record(if Binary.Range(x,0,4)=#binary({0x50,0x4b,0x03,0x04})
then [ Name=BinaryFormat.Text(u16(Binary.Range(x,26,2))),
Extr=BinaryFormat.Binary(u16(Binary.Range(x,28,2))),
Value=BinaryFormat.Transform(BinaryFormat.Binary(u32(Binary.Range(x,18,4))),(y)=>Binary.Decompress(y,Compression.Deflate))]
else []),
lst = BinaryFormat.List(BinaryFormat.Choice(BinaryFormat.Binary(30),get),(x)=>x<>[])(ZIP),
to = Table.FromRecords(List.RemoveLastN(lst,1),type table [Name=text,Value=binary])][to],
from = fxUnzip(file){[Name="customXml/item1.xml"]}?[Value]?,
bin = Binary.FromText(Xml.Document(from){0}[Value],BinaryEncoding.Base64),
data = BinaryFormat.Choice(BinaryFormat.Binary(8),(x)=>BinaryFormat.Binary(u32(Binary.Range(x,4,4))))(bin),
unz = fxUnzip(data){[Name="Formulas/Section1.m"]}[Value],
splt = Text.Split(Text.FromBinary(unz)&"#(cr,lf)",";#(cr,lf)"),
tbl = Table.FromList(List.Range(splt,1,List.Count(splt)-2),f,lst),
to = if from=null then #table(lst,{{null,null}}) else tbl][to],
//собственно подключение к папке
from = Folder.Files("ПУТЬ К ПАПКЕ ВВЕСТИ САМОСТОЯТЕЛЬНО"),
filtr = Table.SelectRows(from, (r)=> (r[Extension] = ".xlsm" or r[Extension] = ".xlsx")),
rem = Table.SelectColumns(filtr,{"Name", "Content"}),
tr = Table.TransformColumns(rem,{"Content",func}),
to = Table.ExpandTableColumn(tr,"Content",lst)
in
to
где
func – собственно функция для вынимания кода запросов (fxUnzip интегрирована внутрь «во избежание»)
from – подключение к папке (увы, я заранее не знаю путь к конкретной папке – его придётся исправить на свой, ручками – если даже это станет проблемой – мои полномочия всё)
filtr, rem – оставили только эксельки и только нужные столбцы
tr,to – преобразовали бинарки файлов в таблицы и развернули их
И всё. Кто найдёт в коде выше File.Contents, без которой якобы не работает – пусть первым бросит в меня камень. Большая просьба думать перед тем, как писать, что что-то «не работает».
Надеюсь, было полезно.
Всех благ!
@buchlotnik
Telegram
Для тех, кто в танке
buchOfficePack – fxUnzip – часть 2 обвес
#АнатомияФункций - buchOfficePack
Всем привет!
Продолжаем пилить библиотеку. Видос уже на Ютубе
Добавили всё по красоте – параметры, справку и залили на гитхаб.
Итоговый код ниже, все мои комментарии в видосе.…
#АнатомияФункций - buchOfficePack
Всем привет!
Продолжаем пилить библиотеку. Видос уже на Ютубе
Добавили всё по красоте – параметры, справку и залили на гитхаб.
Итоговый код ниже, все мои комментарии в видосе.…
Раскидываем иерархию через List.Generate
#АнатомияФункций – List.Generate
Всем привет!
Несколько раз вопрос всплывал в чате и я уже давал образчики кода, но захотелось записать видос – как всегда, на Ютубе.
Код вышел несложный, складываю сюда, чтобы можно было поиском находить
Ну а объяснение по ссылке - смотрим, подписываемся, ставим лайк, пишем комменты. )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#АнатомияФункций – List.Generate
Всем привет!
Несколько раз вопрос всплывал в чате и я уже давал образчики кода, но захотелось записать видос – как всегда, на Ютубе.
Код вышел несложный, складываю сюда, чтобы можно было поиском находить
let
from = Excel.CurrentWorkbook(){[Name="Источник"]}[Content],
lst = List.Buffer(Table.ToList(from,(x)=>x)),
max = List.Max(from[уровень]),
n = List.Count(lst),
gen=List.Generate(
()=>[i=0,j=lst{i},l=List.ReplaceRange(List.Repeat({null},max),j{0}-1,1,{j{1}})&List.Skip(j,2)],
(x)=>x[i]<n,
(x)=>[i=x[i]+1,j=lst{i},l=List.FirstN(x[l],j{0}-1)&{j{1}}&List.Repeat({null},max-j{0})&List.Skip(j,2)],
(x)=>if (lst{x[i]+1}?{0}? ?? 0)>x[j]{0} then null else x[l]
),
nms = List.Transform({1..max},(x)=>"заголовок "&Text.From(x))&List.Skip(Table.ColumnNames(from),2),
to=Table.FromList(List.RemoveNulls(gen),(x)=>x,nms)
in
to
Ну а объяснение по ссылке - смотрим, подписываемся, ставим лайк, пишем комменты. )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
YouTube
03 - На М - Раскидываем иерархию через List.Generate
Решаем достаточно стандартную задачу при ковырянии разных табличек - распределение заголовков из одного столбца по нескольким с учётом уровня иерархии
Кому интересно,
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40
телега тут…
Кому интересно,
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40
телега тут…
Dax Pro Solutions: DAX + Полищук = ОГОНЬ!!!
Всем привет!
Пока я пишу тут про pq и язык М, легендарный Юрий Полищук запустил свой канал по DAX - Dax Pro Solutions
Всё как мы любим – отрыто и бесплатно, НО… файл пример «есть/надо» никто не отменял. Так что всем заинтересованным рекомендую заходить и активно участвовать (прочитав правила, разумеется) . Первые вкусняшки там уже появились.
Надеюсь, было полезно.
Всех благ!
@buchlotnik
Всем привет!
Пока я пишу тут про pq и язык М, легендарный Юрий Полищук запустил свой канал по DAX - Dax Pro Solutions
Всё как мы любим – отрыто и бесплатно, НО… файл пример «есть/надо» никто не отменял. Так что всем заинтересованным рекомендую заходить и активно участвовать (прочитав правила, разумеется) . Первые вкусняшки там уже появились.
Надеюсь, было полезно.
Всех благ!
@buchlotnik
Telegram
Dax Pro Solutions
Dax, Power BI, Data Modelling and Architecture
Получаем несколько элементов списка по их номерам
#АнатомияФункций – List.Transform
Всем привет!
Уже неоднократно поднимался вопрос о том, как получить несколько элементов списка по их номерам. Смотрим код:
Всё просто – НЕЛЬЗЯ писать через запятую в фигурных скобках – так нас не понимают.
А вот через List.Transform – всё очень даже работает. Главное, помнить, что нумерация позиций начинается с нуля, а не с единицы.
Ну и по этому поводу разобрал пару задачек из чата в коротком видео на Ютубе
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#АнатомияФункций – List.Transform
Всем привет!
Уже неоднократно поднимался вопрос о том, как получить несколько элементов списка по их номерам. Смотрим код:
let
from = {"a".."z"},
/*to = from{1,4,8} - так не работает!*/
to = List.Transform({1,4,8},(x)=>from{x-1})
in
to
Всё просто – НЕЛЬЗЯ писать через запятую в фигурных скобках – так нас не понимают.
А вот через List.Transform – всё очень даже работает. Главное, помнить, что нумерация позиций начинается с нуля, а не с единицы.
Ну и по этому поводу разобрал пару задачек из чата в коротком видео на Ютубе
Надеюсь, было полезно.
Всех благ!
@buchlotnik
YouTube
04 - На М - получаем несколько элементов списка по их номерам
Разбираем, как получить НЕСКОЛЬКО элементов списка по их номерам
Кому интересно:
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40
телега тут- https://t.me/pbi_pq_from_tank
Кому интересно:
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40
телега тут- https://t.me/pbi_pq_from_tank
Раскидываем столбец в строки таблицы
#АнатомияФункций – List.Split
Всем привет!
Подогнали мне на днях ссылку на одно видео… Горит до сих пор так, что аж записал видео. Речь идёт об архисложном коде:
Как вы понимаете – это как НАДО делать, а как не надо я даже публиковать тут не буду. Общая мысль – изучать М нужно, чтобы иметь возможность написать ровно то, что надо сделать для решения задачи. Без изысков и подвыпердов.
Детали, мои эмоции и промеры скорости смотрим на Ютубе
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#АнатомияФункций – List.Split
Всем привет!
Подогнали мне на днях ссылку на одно видео… Горит до сих пор так, что аж записал видео. Речь идёт об архисложном коде:
let
from = Excel.CurrentWorkbook(){[Name="Источник2"]}[Content][Сделки],
to = Table.FromList(List.Split(from,9),(x)=>List.FirstN(x,6))
in
to
Как вы понимаете – это как НАДО делать, а как не надо я даже публиковать тут не буду. Общая мысль – изучать М нужно, чтобы иметь возможность написать ровно то, что надо сделать для решения задачи. Без изысков и подвыпердов.
Детали, мои эмоции и промеры скорости смотрим на Ютубе
Надеюсь, было полезно.
Всех благ!
@buchlotnik
YouTube
05 - На М - раскидываем столбец в строки таблицы
Чего-то совсем пригорело от просмотра всякого в интернетах - выкладываю решение задачи по превращению столбца значений в таблицу - кодом, без изысков и подвыпердов
Кому интересно:
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40…
Кому интересно:
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40…
Получаем атрибуты документов Word из папки
#АнатомияФункций - Folder.Files, fxUnzip
Всем привет!
Подкинули недавно задачку – вытащить количество страниц из документов .docx в папке.
Что ж – это повод воспользоваться buchOfficePack.
Код для поиска тут:
А комментарии и объяснения – на Ютубе
Лайк, подписка, коммент приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#АнатомияФункций - Folder.Files, fxUnzip
Всем привет!
Подкинули недавно задачку – вытащить количество страниц из документов .docx в папке.
Что ж – это повод воспользоваться buchOfficePack.
Код для поиска тут:
let
f=(x)=>[a=unzip(x){[Name="docProps/app.xml"]}[Value],
b=Number.From(Xml.Tables(a){0}[Pages])][b],
unzip = Expression.Evaluate(Text.FromBinary(Web.Contents("https://raw.githubusercontent.com/buchlotnik/buchlotnik_functions/main/buchOfficePack")),#shared)[fxUnzip],
from=Folder.Files("путь к папке"),
filtr=Table.SelectRows(from,(r)=>r[Extension]=".docx" and not Text.Contains(r[Name],"~")),
tbl=Table.SelectColumns(filtr,{"Name","Content"}),
tr=Table.TransformColumns(tbl,{"Content",f})
in
tr
А комментарии и объяснения – на Ютубе
Лайк, подписка, коммент приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#sections – собираем результаты всех запросов
#АнатомияФункций – синтаксис
Всем привет!
В очередной раз в чат пришли с вопросом – как собрать результаты всех запросов в одну табличку? Как правило, ситуация осложняется тем фактом, что число запросов может изменяться (чаще добавляются новые).
Что ж – это несложно. Код складываю тут:
А комментарии и объяснения по традиции на Ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#АнатомияФункций – синтаксис
Всем привет!
В очередной раз в чат пришли с вопросом – как собрать результаты всех запросов в одну табличку? Как правило, ситуация осложняется тем фактом, что число запросов может изменяться (чаще добавляются новые).
Что ж – это несложно. Код складываю тут:
let
from = Record.ToTable(Record.RemoveFields(#sections[Section1],"Result")),
nms = List.Distinct(List.Combine(List.Transform(from[Value],Table.ColumnNames))),
to = Table.ExpandTableColumn(from,"Value",nms)
in
to
А комментарии и объяснения по традиции на Ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
YouTube
07 - На М - #sections - собираем результаты всех запросов в один
Решаем задачку по сбору результатов нескольких запросов в одну таблицу, при условии, что список запросов может поменяться
Кому интересно:
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40
телега тут- https://t.me/pbi_pq_from_tank
Кому интересно:
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40
телега тут- https://t.me/pbi_pq_from_tank
ЕЧИСЛО, ЕТЕКСТ и прочие радости с оператором is
#АнатомияФункций - основы
Всем привет!
Вроде несколько раз обсуждали этот вопрос – и тем не менее продолжают возникать затруднения с определением того, принадлежит ли значение к определенному типу (если быть точным – совместим ли тип значения с определённым типом).
По этому поводу код:
Собственно, кроме кода тут и говорить почти нечего – оператор is позволяет осуществить проверку, совместим ли тип значения с соответствующим примитивом, т.е. грубо говоря, позволяет проверить является ли значение числом, текстом, таблицей или функцией. За техническими деталями, без обид, отправляю в справку. А здесь, чисто для иллюстрации, прошу обратить внимание на два последних столбца – все значения совместимы с any (все они чем-то да являются) и ни одно не совместимо с none (т.е. даже null больше, чем просто ничего).
Как-то так – сугубо технический пост, чтобы было на что давать ссылку)))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#АнатомияФункций - основы
Всем привет!
Вроде несколько раз обсуждали этот вопрос – и тем не менее продолжают возникать затруднения с определением того, принадлежит ли значение к определенному типу (если быть точным – совместим ли тип значения с определённым типом).
По этому поводу код:
let
from = #table({"val"},{{null},{1.23},{"текст"},{false},{#date(2024,2,3)},{#datetime(2024,2,3,12,34,56)},{{1..3}},{[a=1]},{#table({},{})},{(x)=>x}}),
lst={ {"ЕПУСТО",(x)=>x=null}, //можно и x is null
{"ЕЧИСЛО",(x)=>x is number},
{"ЕТЕКСТ",(x)=>x is text},
{"ЕЛОГИЧ",(x)=>x is logical},
{"ЕДАТА",(x)=>x is date},
{"ЕДАТАВРЕМЯ",(x)=>x is datetime},
{"ЕСПИСОК",(x)=>x is list},
{"ЕЗАПИСЬ",(x)=>x is record},
{"ЕТАБЛИЦА",(x)=>x is table},
{"ЕФУНКЦИЯ",(x)=>x is function},
{"ЕЧТОУГОДНО",(x)=>x is any},
{"ЕВООБЩЕНИЧЕГО",(x)=>x is none}},
to=List.Accumulate(lst,from,(s,c)=>Table.AddColumn(s,c{0},(r)=>c{1}(Record.Field(r,"val"))))
in
to
Собственно, кроме кода тут и говорить почти нечего – оператор is позволяет осуществить проверку, совместим ли тип значения с соответствующим примитивом, т.е. грубо говоря, позволяет проверить является ли значение числом, текстом, таблицей или функцией. За техническими деталями, без обид, отправляю в справку. А здесь, чисто для иллюстрации, прошу обратить внимание на два последних столбца – все значения совместимы с any (все они чем-то да являются) и ни одно не совместимо с none (т.е. даже null больше, чем просто ничего).
Как-то так – сугубо технический пост, чтобы было на что давать ссылку)))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
Table.Group+GroupKind.Local – локально по нескольким столбцам с агрегацией пачкой
#АнатомияФункций - Table.Group
Всем привет!
В чат закинули простую, но комплексную задачку на группировку. Поэтому поводу складываю сюда код:
А видеоразбор смотрим на Ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#АнатомияФункций - Table.Group
Всем привет!
В чат закинули простую, но комплексную задачку на группировку. Поэтому поводу складываю сюда код:
let
from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
nms = List.Transform(List.Skip(Table.ColumnNames(from),2),(x)=>{x,(t)=>Text.Combine(Table.Column(t,x)," "),type text}),
gr = Table.Group(from,{"Индекс","Index"},nms,GroupKind.Local,(s,c)=>Number.From(c[Index]=4))
in
gr
А видеоразбор смотрим на Ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
YouTube
08 - На М - Table.Group +GroupKind.Local
Решаем задачу локальной группировки по нескольким столбцам с агрегацией пачкой
Кому интересно:
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40
телега тут- https://t.me/pbi_pq_from_tank
Кому интересно:
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40
телега тут- https://t.me/pbi_pq_from_tank
Замена null на ноль пачкой по всем столбцам
#АнатомияФункций - Table.ReplaceValue, Table.TransformColumns
Всем привет!
Вроде тысячу раз уже обсуждали, но всплывает вопрос в комментах – как массово заменить нуллы на нули во всех столбцах таблицы.
Есть варианты:
Мне больше всего нравится последний, но это дело вкуса.
Разбор, как вы догадались, по ссылке
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#АнатомияФункций - Table.ReplaceValue, Table.TransformColumns
Всем привет!
Вроде тысячу раз уже обсуждали, но всплывает вопрос в комментах – как массово заменить нуллы на нули во всех столбцах таблицы.
Есть варианты:
let
from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
to = Table.ReplaceValue(from,null,null,(x,y,z)=>if x=null then 0 else x,Table.ColumnNames(from))
in
to
let
from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
f=(x)=>if x=null then 0 else x,
nms=List.Transform(Table.ColumnNames(from),(x)=>{x,f}),
to = Table.TransformColumns(from,nms)
in
to
let
from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
to = Table.TransformColumns(from,{},(x)=>if x=null then 0 else x)
in
to
Мне больше всего нравится последний, но это дело вкуса.
Разбор, как вы догадались, по ссылке
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
YouTube
09 - На М - замена null на 0 пачкой по всем столбцам
Решаем задачку по замене нуллов на нули во всех столбцах таблицы через Table.ReplaceValue и Table.TransformColumns
Пост про Table.ReplaceValue - https://t.me/pbi_pq_from_tank/213
Пост про Table.TransformColumns - https://t.me/pbi_pq_from_tank/96
мой курс…
Пост про Table.ReplaceValue - https://t.me/pbi_pq_from_tank/213
Пост про Table.TransformColumns - https://t.me/pbi_pq_from_tank/96
мой курс…
Table.ColumnNames + Record.SelectFields - вынимаем нужные поля построчно
#АнатомияФункций - Record.SelectFields
Всем привет!
Подкинули несложную задачку по выниманию полей с ценами и нахождению максимума среди них.
Есть код попроще
А есть посложнее, но железобетоннее:
Что тут к чему рассказывается в видео на Ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#АнатомияФункций - Record.SelectFields
Всем привет!
Подкинули несложную задачку по выниманию полей с ценами и нахождению максимума среди них.
Есть код попроще
let
from = Excel.CurrentWorkbook(){[Name="Обычная"]}[Content],
f=(x)=>List.Max(List.Alternate(List.Skip(Record.ToList(x),4),2,1)),
to = Table.AddColumn(from,"max",f)
in
to
А есть посложнее, но железобетоннее:
let
from = Excel.CurrentWorkbook(){[Name="АД"]}[Content],
lst = List.Buffer(List.Select(Table.ColumnNames(from),(x)=>Text.Contains(x,"Цена"))),
f=(x)=>List.Max(Record.ToList(Record.SelectFields(x,lst))),
to = Table.AddColumn(from,"max",f)
in
to
Что тут к чему рассказывается в видео на Ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
List.Split + List.Zip + List.Combine = таблица
#АнатомияФункций – List
Всем привет!
В чат принесли прикольную задачку на превращение странного куска данных в осмысленную таблицу.
Пришлось резать, крутить, шить и вообще делать всякое. Кода вышло немного:
Ну а что тут к чему, как обычно, рассказывается на Ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#АнатомияФункций – List
Всем привет!
В чат принесли прикольную задачку на превращение странного куска данных в осмысленную таблицу.
Пришлось резать, крутить, шить и вообще делать всякое. Кода вышло немного:
let
f=(x)=>[a=Number.From(Text.Split(x{0}[Column1]," "){4}),
b=List.Combine(Table.ToList(Table.Skip(x,2),(x)=>List.Split(x,2))),
c=List.Transform(b,(x)=>{a}&x)][c],
from = Excel.CurrentWorkbook(){[Name="TData"]}[Content],
gr = Table.Group(from,"Column2",{"tmp",f},GroupKind.Local,(s,c)=>Number.From(c=null)),
to = Table.FromList(List.Zip(gr[tmp]),List.Combine)
in
to
Ну а что тут к чему, как обычно, рассказывается на Ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
YouTube
11 На М Split + Zip + Combine = таблица?
Ковыряем интересную задачку на преобразование странно упакованных данных в адекватную таблицу)))
Ссылка на похожую задачу (попроще): https://t.me/pbi_pq_from_tank/162
И в целом, кому интересно,
мой курс по Power Query - https://akademia-excel.ru/power…
Ссылка на похожую задачу (попроще): https://t.me/pbi_pq_from_tank/162
И в целом, кому интересно,
мой курс по Power Query - https://akademia-excel.ru/power…
Table.Group + List.Generate = сложная пользовательская агрегация
#АнатомияФункций – Table.Group
Всем привет!
Записал очередной разбор задачки из чата – получилось немножко долго, потому как задачка комплексная, но должно быть небезынтересно. Код тут:
Ну а что тут к чему, как обычно, рассказывается на Ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#АнатомияФункций – Table.Group
Всем привет!
Записал очередной разбор задачки из чата – получилось немножко долго, потому как задачка комплексная, но должно быть небезынтересно. Код тут:
let
f=(x)=>[a=List.Transform(nms2,(i)=>List.Sum(Table.Column(x,i))),
b=List.Min(x[Период с]),
c=List.Max(x[Период по]),
gen=List.Generate( ()=>b,
(x)=>x<Date.EndOfMonth(c),
(x)=>Date.AddMonths(x,1),
(x)=>{Date.StartOfMonth(x),Date.EndOfMonth(x),Date.StartOfMonth(x)}),
n=List.Count(gen)-1,
tr=List.ReplaceRange(gen,n,1,{{gen{n}{0},c,gen{n}{0}}}),
tr1=List.ReplaceRange(tr,0,1,{{b,tr{0}{1},tr{0}{2}}&a})][tr1],
from = Excel.CurrentWorkbook(){[Name="М3"]}[Content],
nms1 ={"Период с","Период по","Пеирод месяц"},
nms2 = {"Сумма с НДС","Сумма без НДС","Сумма НДС"},
gr=Table.Group(from,"Контрагент",{{"Площадка",(t)=>Text.Combine(List.Distinct(t[Площадка]),", ")},{"tmp",f}}),
sort=Table.Sort(gr,"Контрагент"),
exp=Table.ExpandListColumn(sort,"tmp"),
splt=Table.SplitColumn(exp,"tmp",(x)=>x,nms1&nms2),
to=Table.TransformColumnTypes(splt,List.Transform(nms1,(x)=>{x,type date})&List.Transform(nms2,(x)=>{x,type number}))
in
to
Ну а что тут к чему, как обычно, рассказывается на Ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
YouTube
13 - На М - Table.Group + List.Generate = сложная пользовательская агрегация
Решаем комплексную задачку на группировку, объединение текстовых полей, генерацию списка дат и вывод строк с разным количеством информации
Пост с кодом - https://t.me/pbi_pq_from_tank_chat/3113
Кому интересно:
мой курс по Power Query - https://akademia…
Пост с кодом - https://t.me/pbi_pq_from_tank_chat/3113
Кому интересно:
мой курс по Power Query - https://akademia…
List.Accumulate vs List.Zip - или нестандартная замена в тексе
#АнатомияФункций – List.Accumulate
Всем привет!
В чат принесли задачку по замене в тексте последовательности из нескольких длинных тире на слеш. Поскольку обычные тримы с реплейсами в этой ситуации не работают, пришлось поколдовать. По этому поводу вариант просто на функциях:
Вариант с if then else:
И вариант с аккумулятором:
Ну а о том, насколько медленный аккумулятор или короче=быстрее, рассказывается, как обычно, рассказывается на Ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#АнатомияФункций – List.Accumulate
Всем привет!
В чат принесли задачку по замене в тексте последовательности из нескольких длинных тире на слеш. Поскольку обычные тримы с реплейсами в этой ситуации не работают, пришлось поколдовать. По этому поводу вариант просто на функциях:
let
f=(x)=>[lst = Text.ToList(x),
zip = Text.Replace(Text.Combine(List.Transform(List.Select(List.Zip({lst,List.Skip(lst)}),(x)=>x<>{"—","—"}),(x)=>x{0})),"—","/")][zip],
from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
to = Table.TransformColumns(from,{"txt",f})
in
to
Вариант с if then else:
let
f=(x)=>[lst = Text.ToList(x),
zip = Text.Combine(List.Transform(List.Zip({lst,List.Skip(lst)}),(x)=>if x{0}<>"—" then x{0} else if x{1}="—" then "" else "/"))][zip],
from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
to = Table.TransformColumns(from,{"txt",f})
in
to
И вариант с аккумулятором:
let
f=(x)=>List.Accumulate(Text.ToList(x),"",(s,c)=>if c<>"—" then s&c else if Text.EndsWith(s,"/") then s else s&"/"),
from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
to = Table.TransformColumns(from,{"txt",f})
in
to
Ну а о том, насколько медленный аккумулятор или короче=быстрее, рассказывается, как обычно, рассказывается на Ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
YouTube
14 - На М - List.Accumulate vs List.Zip - или нестандартная замена в тексте
Кому интересно:
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40
телега тут- https://t.me/pbi_pq_from_tank
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40
телега тут- https://t.me/pbi_pq_from_tank
Table.Group, ListAnyTrue и прочие Splitter.SplitTextByCharacterTransition
#АнатомияФункций - ютуб
Всем привет!
Подкинули прикольную задачку в чат - группировка, словари, текстовые преобразования – получил удовольствие, записал рабор.
Код складываю тут:
Ну а что тут к чему, как обычно, рассказывается на Ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#АнатомияФункций - ютуб
Всем привет!
Подкинули прикольную задачку в чат - группировка, словари, текстовые преобразования – получил удовольствие, записал рабор.
Код складываю тут:
let
lst=List.Buffer({"0".."9"}),
dict = Record.FromTable(Excel.CurrentWorkbook(){[Name="спрСубъекты2"]}[Content]),
f=(x)=>if List.AnyTrue(List.Transform({"R","UA","UB","UC", "UD", "UE", "UF", "UG", "UH", "UI"},(y)=>Text.StartsWith(x,y)))
then Record.Field(dict,Text.Start(Splitter.SplitTextByCharacterTransition((i)=>not List.Contains(lst,i),lst)(x){1},2))
else null,
g=(x)=>if x{0}[reg]=null
then Table.AddColumn(x,"Область",(r)=>null)
else Table.TransformColumns(Table.AddIndexColumn(Table.Sort(x,{"Date","Time"}),"Область",1),{"Область",(x)=>if x=1 then 1 else 0}),
from = Excel.CurrentWorkbook(){[Name="Таблица"]}[Content],
add = Table.AddColumn(from,"reg",(r)=>f(r[HisCall])),
gr = Table.Group(add,{"MyCall","Band","reg"},{"tmp",g}),
tbl = Table.Combine(gr[tmp]),
to = Table.RemoveColumns(tbl,"reg")
in
to
Ну а что тут к чему, как обычно, рассказывается на Ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
YouTube
15 - На М - Table.Group, ListAnyTrue и прочие Splitter.SplitTextByCharacterTransition
Решаем комплексную задачку на группировку, множественную проверку, словари и текстовые преобразования
Кому интересно:
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40
телега тут- https://t.me/pbi_pq_from_tank
Кому интересно:
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40
телега тут- https://t.me/pbi_pq_from_tank
List.Skip, List.FirstN, List.Positions – прикольное упражнение на списки
#АнатомияФункций - List.Skip, List.FirstN, List.Positions
Всем привет!
Подкинули задачку на сборку таблицы из списка букв русского алфавита.
Решил восстановить справедливость - добавил Ё, ну и написал всё по-хулигански:
Ну а что тут к чему, как обычно, рассказывается на Ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#АнатомияФункций - List.Skip, List.FirstN, List.Positions
Всем привет!
Подкинули задачку на сборку таблицы из списка букв русского алфавита.
Решил восстановить справедливость - добавил Ё, ну и написал всё по-хулигански:
[a = List.Buffer({"А".."Е","Ё","Ж".."Я"}),
b = Table.FromList(List.Positions(a),(b)=>List.Skip(a,b)&List.FirstN(a,b)&{a{b}},{" "}&a)][b]
Ну а что тут к чему, как обычно, рассказывается на Ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
YouTube
16 - На М - упражнение на списки
Решаем прикольное упражнение на списки
Кому интересно:
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40
телега тут- https://t.me/pbi_pq_from_tank
Кому интересно:
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40
телега тут- https://t.me/pbi_pq_from_tank
Number.Mod, List.Positions или реализуем код Вижинера
#АнатомияФункций - Number.Mod, List.Positions
Всем привет!
Вчера решали задачу по построению таблицы со сдвигами, но выяснилось, что это требовалось для кодирования информации. Так вот – для кодирования таблица вообще не нужна, нужны только номера, остатки и всякое такое. Поэтому вот код:
А что тут к чему – смотрим на ютубе
Важно – поменял в коде проверку условия – проверяем не null, а именно наличие текста.
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#АнатомияФункций - Number.Mod, List.Positions
Всем привет!
Вчера решали задачу по построению таблицы со сдвигами, но выяснилось, что это требовалось для кодирования информации. Так вот – для кодирования таблица вообще не нужна, нужны только номера, остатки и всякое такое. Поэтому вот код:
(tbl,key,cr)=>
let
lst=List.Buffer({"А".."Е","Ё","Ж".."Я","а".."е","ё","ж".."я","A".."Z","a".."z","0".."9"," ",",",".",":","-"}),
c=if cr=0 then 1 else -1,
d=Record.FromList(List.Positions(lst),lst),
e=List.Buffer(List.Transform(Text.ToList(key),(x)=>Record.Field(d,x))),
n=List.Count(lst),
f=(x)=>if x is text then [a=Text.ToList(x),b=Text.Combine(List.Transform(List.Zip({a,List.Positions(a)}),g))][b] else x,
g=(x)=>lst{Number.Mod(n+Record.Field(d,x{0})+c*(e{Number.Mod(x{1},List.Count(e))}+1),n)},
to=Table.TransformColumns(tbl,{},f)
in
to
А что тут к чему – смотрим на ютубе
Важно – поменял в коде проверку условия – проверяем не null, а именно наличие текста.
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
YouTube
17 - На М - кодируем/декодируем тексты с помощью кода Вижинера
Решаем задачку по кодированию/декодированию информации в таблице с использованием, так называемого, кода Вижинера (многоалфавитный код и придумал его НЕ Вижинер)
пы.сы. уже после записи посетила мысль, что проверять нужно не только null, поэтому правильнее…
пы.сы. уже после записи посетила мысль, что проверять нужно не только null, поэтому правильнее…
Рекурсия, if then else или нестандартная задачка
#АнатомияФункций – рекурсия
Всем привет!
Записал разбор очередной задачки из чата – на этот раз мучаем рекурсивный вызов.
Код:
Что тут к чему – смотрим на ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#АнатомияФункций – рекурсия
Всем привет!
Записал разбор очередной задачки из чата – на этот раз мучаем рекурсивный вызов.
Код:
let
a = PP_титульник[tol],
b = Table.ToList(PostgreSQL_блоки,(x)=>x),
c = List.Count(a),
d = List.Buffer({List.Repeat({null},Table.ColumnCount(PostgreSQL_блоки))&{"fail"}}),
f=(x,y,z,w)=>[k=a{x},l=b{x+y},m=b{x+y+1}?,
n = if x = c then z
else if w then @f(x+1,y,z&d,w)
else if k = l{0} then @f(x+1,y,z&{l&{"no"}},w)
else if k = m{0}? then @f(x+1,y+1,z&{m&{"yes: "&l{0}}},w)
else @f(x+1,y,z&d,true)][n],
e = Table.FromList(f(0,0,{},false),(x)=>x)
in
e
Что тут к чему – смотрим на ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
YouTube
18 - На М - Рекурсия, if then else или нестандартная задачка
Разбираем задачку на рекурсию
Кому интересно:
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40
телега тут- https://t.me/pbi_pq_from_tank
Кому интересно:
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40
телега тут- https://t.me/pbi_pq_from_tank
Рекурсия, Folder.Contents или побеждаем слишком длинные имена файлов
#АнатомияФункций - Folder.Contents
Всем привет!
Продолжаем тему рекурсии. Сегодня побеждаем слишком длинные имена файлов, а для этого вместо Folder.Files используем Folder.Contents и пишем рекурсивный обход содержимого.
Что тут к чему – смотрим на ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#АнатомияФункций - Folder.Contents
Всем привет!
Продолжаем тему рекурсии. Сегодня побеждаем слишком длинные имена файлов, а для этого вместо Folder.Files используем Folder.Contents и пишем рекурсивный обход содержимого.
let
from = Folder.Contents("C:\Users\muzyk\Desktop\папка1"),
f=(x)=>[a=Table.SelectRows(x,(r)=>r[Extension]<>""),
b=Table.SelectRows(x,(r)=>r[Extension]="")[Content],
c=if b={} then x else @f(List.Accumulate(b,a,(s,c)=>[a=try Table.RowCount(c) otherwise 0,
b=if a=0 then s else s&c][b]))][c],
to=f(from)
in
to
Что тут к чему – смотрим на ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
YouTube
19 - На М - Рекурсия, Folder.Contents или побеждаем слишком длинные имена
Продолжаем тему рекурсии. Сегодня побеждаем слишком длинные имена файлов, а для этого вместо Folder.Files используем Folder.Contents и пишем рекурсивный обход содержимого.
Кому интересно:
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40…
Кому интересно:
мой курс по Power Query - https://akademia-excel.ru/powerquery?gcpc=9ae40…
Получаем форматы для ячеек - жирный шрифт, жёлтая заливка и т.д.
#АнатомияФункций - Xml.Tables, Xml.Document
Всем привет!
Пришло время ответить на вопрос - можно ли получить данные только из ячеек, залитых жёлтым, или с красным шрифтом, или жирным и т.п. Глобально – можно., но…
Получилось долго, потому как там всё непросто - так что решайте сами, надо ли вам оно )))
Вот код:
Ну а что тут к чему – смотрим почти час на ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
#АнатомияФункций - Xml.Tables, Xml.Document
Всем привет!
Пришло время ответить на вопрос - можно ли получить данные только из ячеек, залитых жёлтым, или с красным шрифтом, или жирным и т.п. Глобально – можно., но…
Получилось долго, потому как там всё непросто - так что решайте сами, надо ли вам оно )))
Вот код:
let
file = File.Contents("путь/файл.xlsx"),
unz = fxUnzip(file),
book = Table.SelectRows(Excel.Workbook(file,false),(r)=>r[Kind]="Sheet"),
style = Xml.Tables(unz{[Name="xl/styles.xml"]}[Value]),
cellstyle=Table.Buffer(style{[Name="cellXfs"]}[Table]{0}[xf]),
font=style{[Name="fonts"]}[Table]{0}[font],
fill=style{[Name="fills"]}[Table]{0}[fill],
n=1,
sheet=book{n-1}[Data],
sheetdata=Xml.Document(unz{[Name="xl/worksheets/sheet"&Text.From(n)&".xml"]}[Value]){0}[Value]{[Name="sheetData"]}[Value],
f=(x)=>Number.From(x{0}[Attributes]{[Name="s"]}?[Value]?),
tr=Table.TransformColumns(sheetdata,{{"Attributes",(x)=>x{[Name="r"]}[Value]},{"Value",f}}),
dict=Record.FromList(tr[Value],tr[Attributes]),
min=List.Min(List.Transform(tr[Attributes],Number.From)),
add=Table.AddIndexColumn(sheet,"row",min),
tr1=Table.TransformColumns(add,{"row",(x)=>Record.FieldOrDefault(dict,Text.From(x))}),
tr2=Table.TransformColumns(tr1,{"row",(x)=>if x=null then null else cellstyle{x}}),
tr3=Table.TransformColumns(tr2,{"row",(x)=>[a=Number.From(x[#"Attribute:fontId"]?),b=if a=null then null else font{a}[b]][b]}),
skip = Table.PromoteHeaders(Table.Skip(tr3,(r)=>r[Column7]=null)),
to = Table.SelectRows(skip, each ([Column8] = ""))
/*цвет шрифта - tr3=Table.TransformColumns(tr2,{"row",(x)=>[a=Number.From(x[#"Attribute:fontId"]?),b=if a=null then null else font{a}[color]{0}[#"Attribute:rgb"]?][b]})
цвет заливки - tr3=Table.TransformColumns(tr2,{"row",(x)=>[a=Number.From(x[#"Attribute:fillId"]?),b=if a=null then null else fill{a}[patternFill][fgColor]?{0}?{0}?[#"Attribute:rgb"]?][b]})*/
in
to
Ну а что тут к чему – смотрим почти час на ютубе
Лайк, коммент, подписка приветствуются )))
Надеюсь, было полезно.
Всех благ!
@buchlotnik
YouTube
20 - На М - Получаем форматы для ячеек - жирный шрифт, жёлтая заливка и т.д.
Разбираем ответ на вопрос - можно ли получить данные только из ячеек, залитых жёлтым, или с красным шрифтом, или жирным и т.п.
Получилось долго, потому как там всё непросто - так что решайте сами, надо ли вам оно )))
Кому интересно:
мой курс по Power Query…
Получилось долго, потому как там всё непросто - так что решайте сами, надо ли вам оно )))
Кому интересно:
мой курс по Power Query…