Forum Discussion
HELP TO GRAPH multiple data
Step by step...
Select the column and, under the Home tab, click on Split column -> by delimiter
Type in the delimeter in the box, and in advanced options chooe split into rows.
Finally select the column and under the Transform tab, select Format and Trim and then Clean.
ASHISH, TAHNKS a lot for your time
it´s not so simple becose i have more than 1000 dates.
as you can see here i divided them:
when sorted them into the array I need, it brings me only the ones from the first column,
these would be the values placed in the array
I could get a table for each separation but you need all the data in the same table
so I made this table separating each of the variables to take it and cross it with the data
Here it generates the first error
As you can see, an error was generated crossing the information, but in addition to that when I try to make the table, the "pqrs classifications" do not appear in the table.
I appreciate your help BLESSINGS
Following Ashish_Mathur advice to split the column by delimeters into rows, subsitute the code in the advanced editor for the worksheet with the following:
let Origen = Excel.Workbook(File.Contents("D:\Users\pc\Downloads\ReportePQRS_220515.xlsx"), null, true), Worksheet_Sheet = Origen{[Item="Worksheet",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Worksheet_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Id", Int64.Type}, {"Caso", Int64.Type}, {"ID Tecnico", Int64.Type}, {"Nombre Tecnico", type text}, {"Folder", type text}, {"Fecha Recibido", type datetime}, {"Medio Comunicación", type text}, {"Fecha Peticionario", type datetime}, {"Asunto", type text}, {"email", type text}, {"Remitente", type text}, {"Area Remitente", type text}, {"Tipo PQRS", type text}, {"Area PQRS", type text}, {"Clasificación PQRS", type text}, {"Dane Municipio", Int64.Type}, {"Municipio", type text}, {"Dane I.E.", type text}, {"Nombre I.E.", type text}, {"Dane Sede(s)", type text}, {"Nombre Sede(s)", type text}, {"Segmento", Int64.Type}, {"Operador", type text}, {"Contrato", type text}, {"Tipo Ración", type text}, {"Nº TDD Afectados", Int64.Type}, {"Contenido Petición", type text}, {"Fecha Alcance", type datetime}, {"Descripción Alcance", type text}, {"Nº Radicado", type any}, {"Cod. descuento", type any}, {"Requiere mejora", type any}, {"Observación mejora", type any}, {"Requiere Seguimiento", type any}, {"Observación seguimiento", type any}, {"Evidencia Registro", type text}, {"Evidencia Alcance", type text}, {"Gestiones", type text}, {"Estado", Int64.Type}, {"Fecha registro", type datetime}, {"Fecha modificación", type datetime}, {"Clasificación PQRS - Copia.1", type text}, {"Clasificación PQRS - Copia.2", type text}, {"Clasificación PQRS - Copia.3", type text}, {"Clasificación PQRS - Copia.4", type text}}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Clasificación PQRS", Splitter.SplitTextByDelimiter(" / ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Clasificación PQRS"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Clasificación PQRS", type text}}) in #"Changed Type1"I've attached the file for you
Hello, thanks for your time. i can´t see on the attachet, the process, could you show me some pictures about the process.
thank you so much
Step by step...
Select the column and, under the Home tab, click on Split column -> by delimiter
Type in the delimeter in the box, and in advanced options chooe split into rows.
Finally select the column and under the Transform tab, select Format and Trim and then Clean.
Hello, friend, i´ve been learning how to integrate the code that you gave me. is it ok?:
let
Origen = Excel.Workbook(File.Contents("D:\Users\pc\Downloads\ReportePQRS_220515.xlsx"), null, true),
Worksheet_Sheet = Origen{[Item="Worksheet",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Worksheet_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Id", Int64.Type}, {"Caso", Int64.Type}, {"ID Tecnico", Int64.Type}, {"Nombre Tecnico", type text}, {"Folder", type text}, {"Fecha Recibido", type datetime}, {"Medio Comunicación", type text}, {"Fecha Peticionario", type datetime}, {"Asunto", type text}, {"email", type text}, {"Remitente", type text}, {"Area Remitente", type text}, {"Tipo PQRS", type text}, {"Area PQRS", type text}, {"Clasificación PQRS", type text}, {"Dane Municipio", Int64.Type}, {"Municipio", type text}, {"Dane I.E.", type text}, {"Nombre I.E.", type text}, {"Dane Sede(s)", type text}, {"Nombre Sede(s)", type text}, {"Segmento", Int64.Type}, {"Operador", type text}, {"Contrato", type text}, {"Tipo Ración", type text}, {"Nº TDD Afectados", Int64.Type}, {"Contenido Petición", type text}, {"Fecha Alcance", type datetime}, {"Descripción Alcance", type text}, {"Nº Radicado", type any}, {"Cod. descuento", type any}, {"Requiere mejora", type any}, {"Observación mejora", type any}, {"Requiere Seguimiento", type any}, {"Observación seguimiento", type any}, {"Evidencia Registro", type text}, {"Evidencia Alcance", type text}, {"Gestiones", type text}, {"Estado", Int64.Type}, {"Fecha registro", type datetime}, {"Fecha modificación", type datetime}, {"Clasificación PQRS - Copia.1", type text}, {"Clasificación PQRS - Copia.2", type text}, {"Clasificación PQRS - Copia.3", type text}, {"Clasificación PQRS - Copia.4", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Clasificación PQRS", Splitter.SplitTextByDelimiter(" / ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Clasificación PQRS"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Clasificación PQRS", type text}})
in
#"Changed Type1"I cant runed it