Forum Discussion
Transform text lines into date columns
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"
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!
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"
hI WilliamAzevedo , Sorry for the late response. Yes please try out the above code and see if it works as per your requirements. Thanks
Only if there are null values, it would work, but if those extra columns aren't null. What you can do is, while expanding don't use the code but do it manually so it automatically expands into X number of columns in your data.Instead of using the code which has just two columns, just expand it through the icon above, it will automatically expand into the number of columns that you have. Thanks!
your data does not seem to have strictly two rows for a particular project number and month
You are correct, it does not have. The reason is that a project may or may not have it's values readjusted along time (thus some of them have). Sould I try it anyway?
It worked, thank you very much!
Also, thank you, dufoq3, for showing another possibility!