Rateio Unpivot

[chicopra]

Olá, eu tenho uma planilha que é o retorno de um formulário de utilização de veículos, onde o motorista seleciona o veículo que utilizou, informa as quilometragens, porém a utilização do veículo pode ser para mais de uma empresa, então existe uma coluna para ele selecionar uma empresa e na coluna seguinte ele informa o % para determinada empresa:

No mesmo formulário ele pode informar alguma outra despesa da viagem e informar o rateio da mesma forma:

Eu preciso pegar a quantidade de km rodados, calcular um custo de R$ 2 por km e ratear conforme formulário. Também preciso fazer o rateio da despesa.

Como eu faço o unpivot dessas colunas para que eu tenha uma coluna com o nome da empresa das colunas das empresas e outra coluna com os respectivos rateios para os dois critérios (custo km por empresa e rateio de despesa por empresa)?

 

Segue a planilha e PBIX de exemplo, caso ajude.

Obrigado,Teste Tabela Colunas.xlsx

Relatório de Veículos.pbix

[Vitor Peralva]

Bom Dia, @chicopra!

 

Tente o seguinte código, alterando na Fonte para o endereço do seu arquivo no seu computador:

 

let
    Fonte = Excel.Workbook(File.Contents("C:\Users\vitor\Downloads\Teste Tabela Colunas.xlsx"), null, true),
    Tabela1_Table = Fonte{[Item="Tabela1",Kind="Table"]}[Data],
    #"Outras Colunas Não Dinâmicas" = Table.UnpivotOtherColumns(Tabela1_Table, {"veículo", "km inicial", "km fim", "data inicio", "data fim", "despesa"}, "Atributo", "Valores"),
    #"Texto Extraído Antes do Delimitador" = Table.TransformColumns(#"Outras Colunas Não Dinâmicas", {{"Atributo", each Text.BeforeDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}}),
    #"Personalização Adicionada" = Table.AddColumn(#"Texto Extraído Antes do Delimitador", "Colunas", each [
    Tipo = if Text.End([Atributo], 2) = "km" then "Kilometragem" else "Despesa",
    Empresa = if Text.Start([Atributo], 2) = "em" then [Valores] else null,
    Valor = if Empresa <> null then null else if Tipo = "Despesa" then [despesa] * [Valores] else ( ([km fim] - [km inicial]) * 2 ) * [Valores]
]),
    #"Tipo Expandido" = Table.ExpandRecordColumn(#"Personalização Adicionada", "Colunas", {"Tipo", "Empresa", "Valor"}, {"Tipo", "Empresa", "Valor"}),
    #"Preenchido Abaixo" = Table.FillDown(#"Tipo Expandido",{"Empresa"}),
    #"Linhas Filtradas" = Table.SelectRows(#"Preenchido Abaixo", each ([Valor] <> null)),
    #"Colunas Removidas" = Table.RemoveColumns(#"Linhas Filtradas",{"Atributo", "Valores"})
in
    #"Colunas Removidas"

 

[chicopra]

34 minutos atrás, Vitor Peralva disse:

Bom Dia, @chicopra!

 

Tente o seguinte código, alterando na Fonte para o endereço do seu arquivo no seu computador:

 

let
    Fonte = Excel.Workbook(File.Contents("C:\Users\vitor\Downloads\Teste Tabela Colunas.xlsx"), null, true),
    Tabela1_Table = Fonte{[Item="Tabela1",Kind="Table"]}[Data],
    #"Outras Colunas Não Dinâmicas" = Table.UnpivotOtherColumns(Tabela1_Table, {"veículo", "km inicial", "km fim", "data inicio", "data fim", "despesa"}, "Atributo", "Valores"),
    #"Texto Extraído Antes do Delimitador" = Table.TransformColumns(#"Outras Colunas Não Dinâmicas", {{"Atributo", each Text.BeforeDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}}),
    #"Personalização Adicionada" = Table.AddColumn(#"Texto Extraído Antes do Delimitador", "Colunas", each [
    Tipo = if Text.End([Atributo], 2) = "km" then "Kilometragem" else "Despesa",
    Empresa = if Text.Start([Atributo], 2) = "em" then [Valores] else null,
    Valor = if Empresa <> null then null else if Tipo = "Despesa" then [despesa] * [Valores] else ( ([km fim] - [km inicial]) * 2 ) * [Valores]
]),
    #"Tipo Expandido" = Table.ExpandRecordColumn(#"Personalização Adicionada", "Colunas", {"Tipo", "Empresa", "Valor"}, {"Tipo", "Empresa", "Valor"}),
    #"Preenchido Abaixo" = Table.FillDown(#"Tipo Expandido",{"Empresa"}),
    #"Linhas Filtradas" = Table.SelectRows(#"Preenchido Abaixo", each ([Valor] <> null)),
    #"Colunas Removidas" = Table.RemoveColumns(#"Linhas Filtradas",{"Atributo", "Valores"})
in
    #"Colunas Removidas"

 

É exatamente isso. Sensacional. Parabéns e obrigado!