cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Pivote/Despílice después del delimitador, pero mantenga todas las columnas (incluso aquellas sin datos/valores)

Hola a todos y gracias de antemano. La línea de asunto es un buen resumen de mi problema.

1) Tener datos tales como:

JohnA.B.D.
Jimb-e
Janea-e
Josef
JillDf
Joannea-e-e-f

2) Utilice el delimitador para producir:

abcdef
Johnabd
Jimbe
Janeabe
Josef
Jilldf
Joanneabef

3) Desea obtener datos en la columna coincidente. He podido usar algunas opciones de pivote/unpivot (basado en este foro) para llegar a esto:

abdef
Johnabd
Jim b e
Janeab e
Jose f
Jill d f
Joanneab ef

4) El problema: Tenga en cuenta que falta la columna "C". Me gustaría mantenerlo y estoy abierto a otras opciones, así ..... es decir, reorganizar los datos después del paso 2 para obtener los datos en el encabezado de columna coincidente.

Gracias

Josh

1 REPLY 1
Super User IV
Super User IV

Esta es una manera de hacerlo en el editor de consultas. Le sugiero que despope sus columnas, pero esto da la salida deseada creo. Para ver cómo funciona, simplemente cree una consulta en blanco, vaya a Editor avanzado y reemplace el texto allí con el código M a continuación.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUUqsSapJUYrVAQpk5gL5STWpEF5iXipUGiqQXwwSSIOqzckBclJqoNz8xDyEcpBgLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Letters = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Letters", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Lists", each Text.Split([Letters], "|")),
    Custom1 = #"Added Custom",
    #"Removed Other Columns" = Table.SelectColumns(Custom1,{"Name", "Lists"}),
    #"Expanded Lists" = Table.ExpandListColumn(#"Removed Other Columns", "Lists"),
    Custom2 = {"a", "b", "c", "d", "e", "f"},
    #"Converted to Table" = Table.FromList(Custom2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Merged Queries" = Table.NestedJoin(#"Converted to Table", {"Column1"}, #"Expanded Lists", {"Lists"}, "Converted to Table", JoinKind.LeftOuter),
    #"Expanded Converted to Table" = Table.ExpandTableColumn(#"Merged Queries", "Converted to Table", {"Name", "Lists"}, {"Name", "Lists"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Converted to Table", List.Distinct(#"Expanded Converted to Table"[Column1]), "Column1", "Lists"),
    #"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each ([Name] <> null)),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Name", "a", "b", "c", "d", "e", "f"})
in
    #"Removed Other Columns1"

saludos

palmadita





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.