Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
PQ Столбцы в строки (отмена свертования) когда такх строк больше одной (для отмены свертвования)
 
#1
17.06.2019 14:41:56
Добрый день уважаемые коллеги.

Большая просьба подскажите пожалуйста,

В исходной таблице на листе "исходные данные" таблица представлена с двумя горизонтальными строками (план и Факт) и у них идут свои месяца с янв-дек.
Как через квери сделать их этого нармальную таблицу для сводной подскажите пожалуйста. То есть когда просто идут месяц горизонтально, тогда можно через отмену свертвования столбов ереместить их в столбце и база расшириться, но в данном случае когда есть еще одна строка с признаком "факт или план" эту строку то же нао именно в отдельный столбец перевернуть.
 
 
 
#2
17.06.2019 14:55:26
Так нужно?
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Пониженные заголовки" = Table.DemoteHeaders(Источник),
    #"Транспонированная таблица" = Table.Transpose(#"Пониженные заголовки"),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Транспонированная таблица", [PromoteAllScalars=true]),
    #"Несвернутые столбцы" = Table.UnpivotOtherColumns(#"Повышенные заголовки", {"Тип транспорта", "Column2"}, "Атрибут", "Значение")
in
    #"Несвернутые столбцы"
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
#3
17.06.2019 14:58:36
Вау!!!! Спасибо, скажите пожалуйста как вы транспонировали так таблицу???

ЧТо имено транспонировали??
 
 
 
#4
17.06.2019 15:06:06
Доброе время суток.buchlotnik, спешим, а циферки в именах месяцев удалить? :)
Код
let
    digitSymbols = {"0".."9"},
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Тип транспорта", "Месяц"}, {"Column2", "Признак суммы"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Признак суммы", "Месяц"}, "Тип транспорта", "сумма"),
    result = Table.TransformColumns(#"Unpivoted Other Columns", {"Месяц", each Text.Remove(_, digitSymbols)})
in
    result
 
 
 
#5
17.06.2019 15:33:49
Коллеги, попробовал приминить на реальной задаче, к сожелению оказалось, что из за того что в стоблбцах не только тип трансопрта а еще и другие столбцы, транспонировать не получается нармально.

Посмотрите пожалуйста вторую версию файла. Добавил столбец  "Наименование товара".
 
 
 
#6
17.06.2019 15:54:42
Ну такой костылик родился:
Код
let
    digitSymbols = {"0".."9"},
Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Добавлен пользовательский объект" = Table.AddColumn(Источник, "Пользовательская", each [Наименование товара]&"@"&[Тип транспорта]),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Добавлен пользовательский объект",{"Наименование товара", "Тип транспорта"}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Удаленные столбцы",{"Пользовательская", "январь", "февраль", "март", "апрель", "май", "июнь", "июль", "август", "сентябрь", "октябрь", "ноябрь", "декабрь", "январь2", "февраль3", "март4", "апрель5", "май6", "июнь7", "июль8", "август9", "сентябрь10", "октябрь11", "ноябрь12", "декабрь13"}),
    #"Пониженные заголовки" = Table.DemoteHeaders(#"Переупорядоченные столбцы"),
    #"Транспонированная таблица" = Table.Transpose(#"Пониженные заголовки"),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Транспонированная таблица", [PromoteAllScalars=true]),
    #"Несвернутые столбцы" = Table.UnpivotOtherColumns(#"Повышенные заголовки", {"Пользовательская", "Column2"}, "Атрибут", "Значение"),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Несвернутые столбцы", "Атрибут", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"Атрибут.1", "Атрибут.2"}),
    #"Переименованные столбцы" = Table.RenameColumns(#"Разделить столбец по разделителю",{{"Атрибут.1", "Товар"}, {"Атрибут.2", "Транспорт"}, {"Пользовательская", "Месяц"}}),
    result = Table.TransformColumns(#"Переименованные столбцы", {"Месяц", each Text.Remove(_, digitSymbols)})
in
    result
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
#7
17.06.2019 16:38:51
Цитата
buchlotnik написал:
Ну такой костылик родился:
А если там ещё "Грузоподьёмность", "Скорость" и т. д. ?
Как-то так, ориентируясь на null значения в первой строке, думаю, будет лучше
Код
let
    digitSymbols = {"0".."9"},
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    baseInfo = Record.ToTable(Source{0}),
    removeDigits = Table.AddColumn(baseInfo, "Cleaned", each if [Value] = null then null else Text.Remove([Name], digitSymbols)),
    transformList = Table.ToRecords(Table.SelectRows(removeDigits, each [Cleaned] <> null)),
    baseList = Table.SelectRows(removeDigits, each [Cleaned] = null)[Name],
    baseTable = Table.RemoveRows(Source, 0, 1),
    maker = List.Transform(transformList, (rec) =>
    let
        subTable = Table.SelectColumns(baseTable, baseList & {rec[Name]}),
        toSumName = Table.RenameColumns(subTable, {rec[Name], "сумма"}),
        addMonth = Table.AddColumn(toSumName, "Месяц", each rec[Cleaned]),
        addSign = Table.AddColumn(addMonth, "Признак суммы", each rec[Value])

    in
        addSign
    )
in
    Table.Combine(maker)
 
 
 
#8
18.06.2019 18:44:09
Коллеги, я догадался как сделать проще, то есть средствами дилетанта как я.

Объединил строки через разделитель "+". Сначала транспонировав их в столбцы. Только единственное у меня квери сам месяцы переименовал и добавил номера.
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Пониженные заголовки" = Table.DemoteHeaders(Источник),
    #"Транспонированная таблица1" = Table.Transpose(#"Пониженные заголовки"),
    #"Вставлено: объединенный столбец" = Table.AddColumn(#"Транспонированная таблица1", "Сведено", each Text.Combine({[Column1], [Column2]}, "+"), type text),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Вставлено: объединенный столбец",{"Сведено", "Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Переупорядоченные столбцы",{"Column1", "Column2"}),
    #"Транспонированная таблица" = Table.Transpose(#"Удаленные столбцы"),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Транспонированная таблица", [PromoteAllScalars=true]),
    #"Измененный тип" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"Наименование товара", type text}, {"Тип транспорта", type text}, {"январь+План", Int64.Type}, {"февраль+План", Int64.Type}, {"март+План", Int64.Type}, {"апрель+План", Int64.Type}, {"май+План", Int64.Type}, {"июнь+План", Int64.Type}, {"июль+План", Int64.Type}, {"август+План", Int64.Type}, {"сентябрь+План", Int64.Type}, {"октябрь+План", Int64.Type}, {"ноябрь+План", Int64.Type}, {"декабрь+План", Int64.Type}, {"январь2+Факт", Int64.Type}, {"февраль3+Факт", Int64.Type}, {"март4+Факт", Int64.Type}, {"апрель5+Факт", Int64.Type}, {"май6+Факт", Int64.Type}, {"июнь7+Факт", Int64.Type}, {"июль8+Факт", Int64.Type}, {"август9+Факт", Int64.Type}, {"сентябрь10+Факт", Int64.Type}, {"октябрь11+Факт", Int64.Type}, {"ноябрь12+Факт", Int64.Type}, {"декабрь13+Факт", Int64.Type}}),
    #"Несвернутые столбцы" = Table.UnpivotOtherColumns(#"Измененный тип", {"Наименование товара", "Тип транспорта"}, "Атрибут", "Значение"),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Несвернутые столбцы", "Атрибут", Splitter.SplitTextByDelimiter("+", QuoteStyle.Csv), {"Атрибут.1", "Атрибут.2"}),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Разделить столбец по разделителю",{{"Атрибут.1", type text}, {"Атрибут.2", type text}}),
    #"Переименованные столбцы" = Table.RenameColumns(#"Измененный тип1",{{"Атрибут.1", "Мес"}, {"Атрибут.2", "Тип"}, {"Значение", "Сумма"}})
in
    #"Переименованные столбцы"
 
 
 
#9
18.06.2019 20:34:10
Цитата
bobyjoy написал:
Объединил строки через разделитель
а разве я не это предложил? ну и Андрей VG, уже успел поглумиться над этим решением потому что оно не оптимально и, как уже было сказано, представляет собой "костыль"
Изменено: buchlotnik - 18.06.2019 21:05:13
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
#10
18.06.2019 20:49:05
Цитата
buchlotnik написал:
а разве я не это предложил?
Конечно, вы. Но поблагодарить ТС не осмелился :)   Плюс, bobyjoy, хотел решения
Цитата
bobyjoy написал:
а еще и другие столбцы
Но представленное им решение выдаёт для двух добавленных столбцов: Грузоподъёмность и Скорость какую-то хрень.
Изменено: Андрей VG - 18.06.2019 20:49:21
 
 
 
#11
18.06.2019 21:06:26
Цитата
Андрей VG написал:
какую-то хрень
потому что в таком виде нужен постоянный допил напильником, ч.т.д  ;)
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
Страницы: 1
Наверх