Comparación de valores de texto único delimitados en una columna y publicación de un recuento de coincidencias.

[Syndicate_Admin]

Hola

En una tabla de 6000 filas de compunds químicos, tengo una columna con símbolos de elementos químicos separados por ";" en cada celda, por ejemplo, [ H;O; Pb; Ti]

Hay entre 1 y 16 símbolos en cada celda de una lista de 78 elementos válidos en otra tabla.

- estos no están ordenados, por lo que el siguiente puede ser [ Pb;O; H; Ti]

Quiero ejecutar una comparación del contenido de la celda para cada fila (con otras en la tabla) y en una nueva columna publicar un recuento de valores coincidentes, es decir, si el valor (combinación) es único = 1, o si hay un par de coincidencias, el recuento se marca para cada uno = 2 en las filas apropiadas, etc.

El objetivo es comprender la relación entre las combinaciones de elementos en la tabla y su "singularidad", y por lo tanto las relaciones entre compuestos con los mismos elementos,,,,,,,

Estoy seguro de que la forma sencilla de comenzar es ordenar el contenido de la celda, pero tal vez no sea necesario, y luego ejecutar la comparación de matrices.

Agradecería sugerencias con el proceso y/o las funciones/expresiones.

Bien

Bigglerum

[Syndicate_Admin]

¿@Bigglerum

De acuerdo con su declaración, sé que desea contar los mismos valores inculcando valores que se combinan en la misma celda con diferentes tipos, como [ Pb;O; H; Ti] y [ H;O; Pb; Ti].

Pruebe mi camino por código M. Aquí está mi ejemplo.

1. En primer lugar, agregue un Inedx en esta tabla.

2. Divida por ";" en Filas, ordene su nueva tabla por [Columna1].

3. Agrupo todas las filas por [Columna1] , agregue un nuevo índice y expanda la tabla.

4. Agrupar valores por código.

 #"Group Value"= Table.Group(#"Expanded Rows", {"Rows.Index","Rows.Value"}, {{"New", each Text.Combine([Column1], "; "), type text}}),

5. Entonces es fácil contar las filas por igual [Nuevo].

Mi código M:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkiy9rf2sA7JVNJRMlaK1YlWCgYxjcBMD6AcUAFY0hQs4m8NljZXio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Column1", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Column1"}, {{"Rows", each _, type table [Column1=nullable text, Value=nullable text, Index=number]}}),
    #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Added Index1", "Rows", {"Value", "Index"}, {"Rows.Value", "Rows.Index"}),
    #"Group Value"= Table.Group(#"Expanded Rows", {"Rows.Index","Rows.Value"}, {{"New", each Text.Combine([Column1], "; "), type text}}),
    #"Sorted Rows1" = Table.Sort(#"Group Value",{{"Rows.Index", Order.Ascending}}),
    #"Grouped Rows1" = Table.Group(#"Sorted Rows1", {"New"}, {{"Count", each _, type table [Rows.Index=nullable number, Rows.Value=nullable text, New=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.RowCount([Count])),
    #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom", "Count", {"Rows.Index", "Rows.Value"}, {"Count.Rows.Index", "Count.Rows.Value"}),
    #"Sorted Rows2" = Table.Sort(#"Expanded Count",{{"Count.Rows.Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows2",{"Count.Rows.Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Count.Rows.Value", "Value"}})
in
    #"Renamed Columns"

Saludos
Rico Zhou

Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.

[Syndicate_Admin]

Hi Rico,

Gracias, realmente estoy luchando con esto: entiendo las funciones y lo que está sucediendo con su código, mi debilidad es ser un principiante y tratar de transponer su consulta a mis datos.
¡Esto comienza con la definición de la fuente!

Tu código dice

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkiy9rf2sA7JVNJRMlaK1YlWCgYxjcBMD6AcUAFY0hQs4m8NljZXio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Value = _t]),

Necesito obtener un origen de un csv con una columna Necesito nombrar ElementsRaw - aquí hay una consulta que importa los datos y genera la columna ElementsRaw

dejar
Source = Csv.Document(File.Contents("D:\Users\Mark\Documents\unique1.csv"),[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Encabezados promocionados" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Encabezados promocionados",{{"Formula", type text}, {"Mineral Count", Int64.Type}}),
#"Columnas renombradas" = Table.RenameColumns(#"Changed Type",{{"Formula", "ElementsRaw"}, {"Mineral Count", "Minerals with Same Complexity"}})
en
#"Columnas renombradas"

Estoy seguro de que una vez que consiga este trabajo fundamental podré continuar


Saludos

Bigglerum

[Syndicate_Admin · Accepted solution]

@Bigglerum ,

Obtengo datos de CSV y le muestro cómo actualizar su código.

Aquí está mi código original como el tuyo.

let
    Source = Csv.Document(File.Contents("...\unique1.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Value", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "ElementsRaw"}})
in
    #"Renamed Columns"

Mi muestra:

Agregue códigos después del paso "Columnas renombradas".

let
    Source = Csv.Document(File.Contents("...\unique1.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Value", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "ElementsRaw"}}),
    #"Added Index" = Table.AddIndexColumn( #"Renamed Columns", "Index", 1, 1, Int64.Type),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"ElementsRaw", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ElementsRaw"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ElementsRaw", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"ElementsRaw", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ElementsRaw"}, {{"Rows", each _, type table [ElementsRaw=nullable text, Value=nullable text, Index=number]}}),
    #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Added Index1", "Rows", {"Value", "Index"}, {"Rows.Value", "Rows.Index"}),
    #"Group Value"= Table.Group(#"Expanded Rows", {"Rows.Index","Rows.Value"}, {{"New", each Text.Combine([ElementsRaw], "; "), type text}}),
    #"Sorted Rows1" = Table.Sort(#"Group Value",{{"Rows.Index", Order.Ascending}}),
    #"Grouped Rows1" = Table.Group(#"Sorted Rows1", {"New"}, {{"Count", each _, type table [Rows.Index=nullable number, Rows.Value=nullable text, New=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.RowCount([Count])),
    #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom", "Count", {"Rows.Index", "Rows.Value"}, {"Count.Rows.Index", "Count.Rows.Value"}),
    #"Sorted Rows2" = Table.Sort(#"Expanded Count",{{"Count.Rows.Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows2",{"Count.Rows.Index"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Count.Rows.Value", "Value"}})
in
    #"Renamed Columns2"

El resultado es el siguiente.

Saludos
Rico Zhou

Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.

[Syndicate_Admin]

Hola Alexis,

Gracias de nuevo, tal vez necesito buscar un desarrollador para esto, se siente un poco avanzado y necesito construir algo robusto!

Me encanta Powerbi y desearía tener las habilidades y el tiempo para dar para convertirme en un experto.

Gracias de nuevo

Bigglerum

[Syndicate_Admin]

No parece más que un simple juego de transformación y filtrado de datos. Pegue suficiente conjunto de datos ficticios, le mostraré el camino.

[Syndicate_Admin]

Quizás. Esta sería una buena experiencia de aprendizaje, pero algo desalentadora si eres newish a DAX y conceptos de modelado.

Esto parece un desafío interesante, por lo que si tiene un archivo de muestra al que puede vincular (no necesita ser datos reales), me interesaría jugar con él de vez en cuando para mantenerlo avanzando.

[Syndicate_Admin]

Hola Alex,
Eso es muy amable de ustedes: el proyecto es en realidad una empresa sin fines de lucro que mi empresa está financiando en Educación Científica.

Felizmente compartiremos: ¿se nos permite hacer esto fuera del foro? Soy nuevo aquí, así que no estoy seguro de lo que es legítimo.

Saludos

Bigglerum

[Syndicate_Admin]

La forma más común de compartir archivos es publicar un enlace a una copia guardada en la nube (por ejemplo, Dropbox / Google Drive / SharePoint / OneDrive).

[Syndicate_Admin]

He demostrado cómo puedes comparar listas delimitadas como esta independientemente del orden aquí:
https://community.powerbi.com/t5/Desktop/Match-between-two-columns-in-different-order/m-p/2204459

Sin embargo, probablemente pueda hacer su vida mucho más fácil si divide esa columna por delimitador en filas a través del editor de consultas:

Con esto, puede crear una relación ValidElements[Element] 1--> * Compounds[Element] y analizar patrones y relaciones debería ser más fácil.

[Syndicate_Admin]

 

Hola Alexis,

Estoy muy agradecido por su ayuda y sugerencias.

Mirando el enlace a la solución anterior, esto apunta a dos valores de columna uno al lado del otro, en mis datos los valores están en una columna en 6000 filas impares.

¿No estoy seguro de poder usar el enfoque de división?

Saludos

Bigglerum

[Syndicate_Admin]

El enlace que publiqué no es exactamente lo que necesitas. Simplemente da el sabor para comparar cadenas delimitadas.

Le sugiero que expanda su sola columna a través de filas de 6k a una sola columna que divida cada lista de elementos en tantas filas como elementos haya. La nueva tabla tendrá 6k * (elementos promedio por compuesto) filas.