Transform text lines into date columns

Hi. I have a budget control that must show how much money was budgeted and how much was spent and compare both using a columns and line visual. The problem is my data has no date because the mont...

Need Help

SundarRaj
1 year ago
Please try this WilliamAzevedo
What I think the reason could be is, that your data does not seem to have strictly two rows for a particular project number and month i.e. The Budget and The Spent. Apart from these two, there are certain rows for a particular project number and month which are null as well that get picked up while expanding when just "Column1" and "Column2" are asked to be picked up by the code. I'll leave the changed code where I have tried remove any null values from the list after grouping ( List.RemoveNulls(_[Value]) ) which eventually expand just the rows with numbers. Thanks!

let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", Int64.Type}}),

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project"}, "Attribute", "Value"),

Custom1 = Table.TransformColumns(#"Unpivoted Other Columns", {"Attribute", each Text.End(_,3)}),

#"Grouped Rows" = Table.Group(Custom1, {"Project", "Attribute"}, {{"All", each _[Value]}}),

Custom2 = Table.TransformColumns(#"Grouped Rows",{"All", each List.RemoveNulls(_)}),

#"Extracted Values" = Table.TransformColumns(Custom2, {"All", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),

#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "All", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"All.1", "All.2"}),

#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"All.1", type text}, {"All.2", type text}})

in

#"Changed Type1"

WilliamAzevedo

Advocate II

1 year ago

Unfortunately it's not 🙁.

But, as you can see, I have other data sources and for the help I'm asking here I'm working specifically with queries fZRIM0001 - Orçado x Realizado and it's derivate (linked), dMetas Mensais, which I'm asking help for. What if I send you the code for them?

fZRIM0001 - Orçado x Realizado

let
    Fonte = SharePoint.Files("https://company.sharepoint.com/sites/divisão", [ApiVersion = 15]),
    #"Linhas Filtradas" = Table.SelectRows(Fonte, each ([Folder Path] = "https://company.sharepoint.com/sites/divisão/Documentos Partilhados/Relatórios de Gestão/Rel_Inv - Orc_Real/")),
    #"Linhas Filtradas1" = Table.SelectRows(#"Linhas Filtradas", let latest = List.Max(#"Linhas Filtradas"[Date created]) in each [Date created] = latest),
    #"orc_x_real XLS xlsx_https://company sharepoint com/sites/divisão/Documentos Partilhados/Relatórios de Gestão/Rel_Inv - Orc_Real/" = #"Linhas Filtradas1"{[Name="orc_x_real.XLS.xlsx",#"Folder Path"="https://company.sharepoint.com/sites/divisão/Documentos Partilhados/Relatórios de Gestão/Rel_Inv - Orc_Real/"]}[Content],
    #"Pasta de Trabalho Importada do Excel" = Excel.Workbook(#"orc_x_real XLS xlsx_https://company sharepoint com/sites/divisão/Documentos Partilhados/Relatórios de Gestão/Rel_Inv - Orc_Real/"),
    orc_x_real_Sheet = #"Pasta de Trabalho Importada do Excel"{[Item="orc_x_real",Kind="Sheet"]}[Data],
    #"Colunas Removidas" = Table.RemoveColumns(orc_x_real_Sheet,{"Column1"}),
    #"Linhas Filtradas2" = Table.SelectRows(#"Colunas Removidas", each ([Column2] <> null)),
    #"Cabeçalhos Promovidos" = Table.PromoteHeaders(#"Linhas Filtradas2", [PromoteAllScalars=true]),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Cabeçalhos Promovidos",{{"Invest.", Int64.Type}, {"Descrição Investimento", type text}, {"Diret.", type text}, {"Aplic.", type text}, {"Descrição Finalidade", type text}, {"Unidade Gestora.", type text}, {"Sistema", type text}, {"Descrição Agrupamento", type text}, {"C. Pcte.", Int64.Type}, {"Descrição Pacote GMI", type text}, {"C.Munic.", type text}, {"Município", type text}, {"Fonte", type text}, {"Tipo de Fonte", type text}, {"Reaj.", type logical}, {"Exerc.", Int64.Type}, {"     Orç. Jan", type number}, {"  Real. Jan", type number}, {"  Orç. Fev", type number}, {"  Real. Fev", type number}, {"  Orç. Mar", type number}, {"  Real. Mar", type number}, {"  Orç. Abr", type number}, {"Real.Abr", Int64.Type}, {"  Orç. Mai", type number}, {"Real.Mai", Int64.Type}, {"  Orç. Jun", type number}, {"Real.Jun", Int64.Type}, {"  Orç. Jul", type number}, {"Real.Jul", Int64.Type}, {"  Orç. Ago", type number}, {"Real.Ago", Int64.Type}, {"  Orç. Set", type number}, {"Real.Set", Int64.Type}, {"   Orç. Out", type number}, {"Real.Out", Int64.Type}, {"   Orç. Nov", type number}, {"Real.Nov", Int64.Type}, {"     Orç. Dez", type number}, {"Real.Dez", Int64.Type}, {" Total Orçado", type number}, {"      Alocado", type number}, {"    Tot Real.", type number}, {" Saldo do PIE", type number}, {"   Saldo Aloc", type number}, {"Excluído", type text}, {"Tp Ct Del", type text}, {"Contrt. Deleg", type text}, {"Delegação", type text}, {"Programa", type text}, {"Cd. Fonte", Int64.Type}, {"CD TERRIT", Int64.Type}, {"Territ. Id", type text}, {"Semiárido", type text}, {"Microrregião", type text}, {"InvCap", type text}, {"Status Inv.", type text}, {"EVTE", type text}, {"ERNAM", type text}, {"ERDAT", Int64.Type}, {"AENAM", type text}, {"AEDAT", Int64.Type}, {"Tp.Invest.", Int64.Type}, {"Des.TpInv", type text}}),
    #"Linhas Classificadas" = Table.Sort(#"Tipo Alterado",{{"Invest.", Order.Ascending}}),
    #"Colunas Renomeadas" = Table.RenameColumns(#"Linhas Classificadas",{{"     Orç. Jan", "Orç. Jan"}, {"  Real. Jan", "Real. Jan"}, {"  Orç. Fev", "Orç. Fev"}, {"  Real. Fev", "Real. Fev"}, {"  Orç. Mar", "Orç. Mar"}, {"  Real. Mar", "Real. Mar"}, {"  Orç. Abr", "Orç. Abr"}, {"  Orç. Mai", "Orç. Mai"}, {"  Orç. Jun", "Orç. Jun"}, {"  Orç. Jul", "Orç. Jul"}, {"  Orç. Ago", "Orç. Ago"}, {"  Orç. Set", "Orç. Set"}, {"   Orç. Out", "Orç. Out"}, {"   Orç. Nov", "Orç. Nov"}, {"     Orç. Dez", "Orç. Dez"}, {" Total Orçado", "Total Orçado"}, {"      Alocado", "Alocado"}, {"    Tot Real.", "Tot Real."}, {" Saldo do PIE", "Saldo do PIE"}, {"   Saldo Aloc", "Saldo Aloc"}}),
    #"Tipo Alterado1" = Table.TransformColumnTypes(#"Colunas Renomeadas",{{"Orç. Jan", type number}, {"Real. Jan", type number}, {"Orç. Fev", type number}, {"Real. Fev", type number}, {"Orç. Mar", type number}, {"Real. Mar", type number}, {"Orç. Abr", type number}, {"Real.Abr", type number}, {"Orç. Mai", type number}, {"Real.Mai", type number}, {"Orç. Jun", type number}, {"Real.Jun", type number}, {"Orç. Jul", type number}, {"Real.Jul", type number}, {"Orç. Ago", type number}, {"Real.Ago", type number}, {"Orç. Set", type number}, {"Real.Set", type number}, {"Orç. Out", type number}, {"Real.Out", type number}, {"Orç. Nov", type number}, {"Real.Nov", type number}, {"Orç. Dez", type number}, {"Real.Dez", type number}}),
    #"Linhas Classificadas1" = Table.Sort(#"Tipo Alterado1",{{"Invest.", Order.Ascending}}),
    #"Erros Substituídos" = Table.ReplaceErrorValues(#"Linhas Classificadas1", {{"Reaj.", true}}),
    #"Colunas Renomeadas1" = Table.RenameColumns(#"Erros Substituídos",{{"Real.Abr", "Real. Abr"}, {"Real.Mai", "Real. Mai"}, {"Real.Jun", "Real. Jun"}, {"Real.Jul", "Real. Jul"}, {"Real.Ago", "Real. Ago"}, {"Real.Set", "Real. Set"}, {"Real.Out", "Real. Out"}, {"Real.Nov", "Real. Nov"}, {"Real.Dez", "Real. Dez"}})
in
    #"Colunas Renomeadas1"

dMetas Mensais (so far):

let
    Fonte = #"fZRIM0001 - Orçado x Realizado",
    #"Outras Colunas Removidas" = Table.SelectColumns(Fonte,{"Invest.", "Orç. Jan", "Real. Jan", "Orç. Fev", "Real. Fev", "Orç. Mar", "Real. Mar", "Orç. Abr", "Real. Abr", "Orç. Mai", "Real. Mai", "Orç. Jun", "Real. Jun", "Orç. Jul", "Real. Jul", "Orç. Ago", "Real. Ago", "Orç. Set", "Real. Set", "Orç. Out", "Real. Out", "Orç. Nov", "Real. Nov", "Orç. Dez", "Real. Dez"}),
    #"Outras Colunas Não Dinâmicas" = Table.UnpivotOtherColumns(#"Outras Colunas Removidas", {"Invest."}, "Atributo", "Valor"),
    #"Custom1" = Table.TransformColumns(#"Outras Colunas Não Dinâmicas", {"Atributo", each Text.End(_,3)}),
    #"Grouped Rows" = Table.Group(#"Custom1", {"Invest.", "Atributo"}, {{"Todos", each Table.FromRows({_[Valor]})}}),
    #"Expanded Todos" = Table.ExpandTableColumn(#"Grouped Rows", "Todos", {"Column1", "Clumn2"}, {"Column1", "Column2"})
in
    #"Expanded Todos"

Would it work? Thank you!

SundarRaj

Super User

1 year ago

Please try this WilliamAzevedo
What I think the reason could be is, that your data does not seem to have strictly two rows for a particular project number and month i.e. The Budget and The Spent. Apart from these two, there are certain rows for a particular project number and month which are null as well that get picked up while expanding when just "Column1" and "Column2" are asked to be picked up by the code. I'll leave the changed code where I have tried remove any null values from the list after grouping ( List.RemoveNulls(_[Value]) ) which eventually expand just the rows with numbers. Thanks!

let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", Int64.Type}}),

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project"}, "Attribute", "Value"),

Custom1 = Table.TransformColumns(#"Unpivoted Other Columns", {"Attribute", each Text.End(_,3)}),

#"Grouped Rows" = Table.Group(Custom1, {"Project", "Attribute"}, {{"All", each _[Value]}}),

Custom2 = Table.TransformColumns(#"Grouped Rows",{"All", each List.RemoveNulls(_)}),

#"Extracted Values" = Table.TransformColumns(Custom2, {"All", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),

#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "All", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"All.1", "All.2"}),

#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"All.1", type text}, {"All.2", type text}})

#"Changed Type1"

Forum Discussion

Transform text lines into date columns

Related content

Transform columns / type

Table Transformation

Transform Column / Type

Transpose/ Transform Rows to Columns

Transform Text Column into Date Column