March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I very kindly request information, for the following inconvenience
HELP FOR GRAPHING MULTIPLE DATA IN ONE CEL
I have
try to separate the columns by character / and it only brings me in the graph the first data found in the first column.
how could i graph this?
ty for your help
Solved! Go to Solution.
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.
Proud to be a Super User!
Paul on Linkedin.
Hi,
In the Query Editor, split the second column by rows with / as a delimiter. Now create your desired visual.
THANKS FOR YOUR TIME.
I have to do the analisys of that data and i have a problem...
One of those cells have multiple data in one cell for example
ID DATA
X A / B / C / D
Y C / D
I need know how many have each ID in ESPECIFIC data and get that in a table:
A B C D TOTAL
X 1 1 1 1 4
Y 1 1 2
6
I tried to do that with split column but the table just give me the first data found in the first column
A B C D TOTAL
X 1 1
Y 1 1
2
I managed to do the exercise by making a table by column, but I need the data in a single table like this:
A B C D TOTAL
X 1 1 1 1 4
Y 1 1 2
6
Hi!
First open the query editor in here:
So, you're going to have this:
Right-click on the top of the column, then go to "split column" and "by delimiter":
So, you gonna see this screen:
The final result you gonna have:
Return to Power BI with a left click here:
Regards!
https://www.youtube.com/channel/UC9nTjkTNOhnQxHj57tj45lw
Hola, hablas español?
HELLO. ty for your time...
i have to do the analisys of that data and i have a problem...
One of those cells have multiple data in one cell for example
ID DATA
X A / B / C / D
Y C / D
I need know how many have each ID in ESPECIFIC data and get that in a table:
A B C D TOTAL
X 1 1 1 1 4
Y 1 1 2
6
I tried to do that with split column but the table just give me the first data found in the first column
A B C D TOTAL
X 1 1
Y 1 1
2
I managed to do the exercise by making a table by column, but I need the data in a single table like this:
A B C D TOTAL
X 1 1 1 1 4
Y 1 1 2
6
Hi @ipso ,
In query editor, let's start with base data (sample):
Then split column as below:
that's it.
Then close and apply the changes of query editor.
Now u can use this table data in matrix visual to get exact same format data you intend to make:
I did this with a visual, and the table created is long format. I think this is fine, if you really want to take advantage of dimensional design, by later relating a dim table with DATA fields.
Pls let me know if your desired outpt must be in the table only.
Hope it helps.
Hi,
You may download my PBI file from here.
Hope this helps.
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
I do not understand. My reply definitely solved your initial question. This seems to be a new requirement now. Furthermore since all information is in Spanish, I cannot understand your requirement either.
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
Proud to be a Super User!
Paul on Linkedin.
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
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.
Proud to be a Super User!
Paul on Linkedin.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
90 | |
86 | |
76 | |
49 |
User | Count |
---|---|
167 | |
149 | |
99 | |
73 | |
57 |