Hi, everyone!
I've been struggling with this. When I export data from this excel sheet, some lines does not show up. They are missing and I am trying to figure out if any of these is comands are responsible for this issue. Here it is:
let
#"Linhas Filtradas" = Table.SelectRows(Fonte, each ([Name] = "Process Brazil Capability.xlsx")),
Engagements_Sheet = Source{[Item="Engagements",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Engagements_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Engagement #", type text}, {"Engagement Name", type text}, {"Client", type text}, {"Site A", type text}, {"Site B", type text}, {"Site C", type text}, {"Confidentiality", type text}, {"Year of completion", type any}, {"Phase A", type text}, {"Phase B", type text}, {"Phase C", type text}, {"Phase D", type text}, {"Scope", type text}, {"Resource - EM", type text}, {"Resource - Process Lead", type text}, {"Resource - Process Support A", type text}, {"Resource - Process Support B", type text}, {"Resource - Process Support C", type text}, {"Resource - Process Support D", type text}, {"Project Location", type text}, {"xx Office (SAP)", type text}, {"Practice A", type text}, {"Practice B", type text}, {"Practice C", type text}, {"Commodity A", type text}, {"Commodity B", type text}, {"Commodity C", type text}, {"Software A", type text}, {"Software B", type text}, {"Software C", type text}, {"Process unit A", type text}, {"Process unit B", type text}, {"Process unit C", type text}, {"Process unit D", type text}, {"Process unit E", type text}, {"Process unit F", type text}, {"Process unit G", type text}, {"Process unit H", type text}, {"Process unit I", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Engagement #", "Engagement Name", "Client", "Site A", "Site B", "Site C", "Confidentiality", "Year of completion", "Phase A", "Phase B", "Phase C", "Phase D", "Scope", "Project Location", "xxx Office (SAP)", "Practice A", "Practice B", "Practice C", "Commodity A", "Commodity B", "Commodity C", "Software A", "Software B", "Software C", "Process unit A", "Process unit B", "Process unit C", "Process unit D", "Process unit E", "Process unit F", "Process unit G", "Process unit H", "Process unit I"}, "Attribute", "Value"),
#"Linhas Filtradas1" = Table.SelectRows(#"Unpivoted Columns", each true),
#"Renamed Columns" = Table.RenameColumns(#"Linhas Filtradas1",{{"Attribute", "Resource_A"}, {"Value", "Resource_V"}}),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Engagement #", "Engagement Name", "Client", "Site A", "Site B", "Site C", "Confidentiality", "Year of completion", "Phase A", "Phase B", "Phase C", "Phase D", "Scope", "Project Location", "xxx Office (SAP)", "Practice A", "Practice B", "Practice C", "Software A", "Software B", "Software C", "Process unit A", "Process unit B", "Process unit C", "Process unit D", "Process unit E", "Process unit F", "Process unit G", "Process unit H", "Process unit I", "Resource_A", "Resource_V"}, "Attribute", "Value"),
#"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Columns1",{{"Attribute", "Commodity_A"}, {"Value", "Commodity_V"}}),
#"Unpivoted Columns2" = Table.UnpivotOtherColumns(#"Renamed Columns1", {"Engagement #", "Engagement Name", "Client", "Site A", "Site B", "Site C", "Confidentiality", "Year of completion", "Scope", "Project Location", "xxx Office (SAP)", "Practice A", "Practice B", "Practice C", "Software A", "Software B", "Software C", "Process unit A", "Process unit B", "Process unit C", "Process unit D", "Process unit E", "Process unit F", "Process unit G", "Process unit H", "Process unit I", "Resource_A", "Resource_V", "Commodity_A", "Commodity_V"}, "Attribute", "Value"),
#"Renamed Columns2" = Table.RenameColumns(#"Unpivoted Columns2",{{"Attribute", "Phase_A"}, {"Value", "Phase_V"}}),
#"Unpivoted Columns3" = Table.UnpivotOtherColumns(#"Renamed Columns2", {"Engagement #", "Engagement Name", "Client", "Site A", "Site B", "Site C", "Confidentiality", "Year of completion", "Scope", "Project Location", xx Office (SAP)", "Practice A", "Practice B", "Practice C", "Software A", "Software B", "Software C", "Resource_A", "Resource_V", "Commodity_A", "Commodity_V", "Phase_A", "Phase_V"}, "Attribute", "Value"),
#"Renamed Columns3" = Table.RenameColumns(#"Unpivoted Columns3",{{"Attribute", "Operations_A"}, {"Value", "Operations_V"}}),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Year of completion", Int64.Type}}),
#"Tipo Alterado2" = Table.TransformColumnTypes(#"Tipo Alterado",{{"Year of completion", type text}}),
#"Valor Substituído" = Table.ReplaceValue(#"Tipo Alterado2","Resource - EM","EM",Replacer.ReplaceText,{"Resource_A"}),
#"Valor Substituído1" = Table.ReplaceValue(#"Valor Substituído","Resource - Process Lead","PL",Replacer.ReplaceText,{"Resource_A"}),
#"Valor Substituído2" = Table.ReplaceValue(#"Valor Substituído1","Resource - Process Support A","Support A",Replacer.ReplaceText,{"Resource_A"}),
#"Valor Substituído3" = Table.ReplaceValue(#"Valor Substituído2","Resource - Process Support B","Support B",Replacer.ReplaceText,{"Resource_A"}),
#"Valor Substituído4" = Table.ReplaceValue(#"Valor Substituído3","Resource - Process Support C","EQ",Replacer.ReplaceText,{"Resource_A"}),
#"Valor Substituído5" = Table.ReplaceValue(#"Valor Substituído4","Support A","EQ",Replacer.ReplaceText,{"Resource_A"}),
#"Valor Substituído6" = Table.ReplaceValue(#"Valor Substituído5","Support B","EQ",Replacer.ReplaceText,{"Resource_A"}),
#"Valor Substituído7" = Table.ReplaceValue(#"Valor Substituído6","Resource - Process Support D","EQ",Replacer.ReplaceText,{"Resource_A"}),
#"Tipo Alterado1" = Table.TransformColumnTypes(#"Valor Substituído7",{{"Year of completion", type text}}),
#"Valor Substituído8" = Table.ReplaceValue(#"Tipo Alterado1","Error","Ongoing",Replacer.ReplaceText,{"Year of completion"})
in
#"Valor Substituído8"
