Crea varias filas a partir de columnas

Hola,

Actualmente estoy trabajando con un conjunto de datos que proviene de uno de los archivos de Excel de nuestras oficinas de proyectos. Los datos tienen 27 columnas, necesito tomar las últimas 14 columnas y crear nuevas filas para esas entradas y mantener los datos en las primeras 15 columnas. En Excel, buscaría usar un VBA para alterar los datos, pero preferiría hacer esto por mi parte en Power BI para no transformar sus datos y la forma en que los están usando.

La solución Univot en el editor de consultas se vería así:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLvErzU1KLVLSUQrISCxOVTAEspxLi4pS80qALLfSvJTUFLikETbJWJ1oJUcnZ0OQJEi3oYEBkDQFESAhIzALJAhS6OziamJqBlVpZGAAV28E1mMAFY2NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProjectNumber = _t, #"Phase 1" = _t, Current = _t, Funded = _t, Phase2 = _t, asdf = _t, Column2 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ProjectNumber", type text}, {"Phase 1", Int64.Type}, {"Current", Int64.Type}, {"Funded", Int64.Type}, {"Phase 2", Int64.Type}, {"Current_1", Int64.Type}, {"Funded_2", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ProjectNumber"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Split Column by Delimiter", {{"Attribute.1", each Text.BeforeDelimiter(_, "_"), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Attribute.2", "Phase"}}),
    #"Filled Down" = Table.FillDown(#"Renamed Columns",{"Phase"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Attribute.1] <> "Phase")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute.1]), "Attribute.1", "Value")
in
    #"Pivoted Column"

Esto sigue el método que describí aquí: https://www.thebiccountant.com/2015/08/12/how-to-pivot-multiple-measurescolumns-in-power-query/

Hola @jritchey

Consulte la función de desvío en el editor de consultas.

Si eso no funciona, pegue una muestra de sus datos: antes y después.

El uso de Univot no funciona. Lo que estoy tratando de hacer con la consulta es esto:

La solución Univot en el editor de consultas se vería así:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLvErzU1KLVLSUQrISCxOVTAEspxLi4pS80qALLfSvJTUFLikETbJWJ1oJUcnZ0OQJEi3oYEBkDQFESAhIzALJAhS6OziamJqBlVpZGAAV28E1mMAFY2NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProjectNumber = _t, #"Phase 1" = _t, Current = _t, Funded = _t, Phase2 = _t, asdf = _t, Column2 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ProjectNumber", type text}, {"Phase 1", Int64.Type}, {"Current", Int64.Type}, {"Funded", Int64.Type}, {"Phase 2", Int64.Type}, {"Current_1", Int64.Type}, {"Funded_2", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ProjectNumber"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Split Column by Delimiter", {{"Attribute.1", each Text.BeforeDelimiter(_, "_"), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Attribute.2", "Phase"}}),
    #"Filled Down" = Table.FillDown(#"Renamed Columns",{"Phase"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Attribute.1] <> "Phase")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute.1]), "Attribute.1", "Value")
in
    #"Pivoted Column"

Esto sigue el método que describí aquí: https://www.thebiccountant.com/2015/08/12/how-to-pivot-multiple-measurescolumns-in-power-query/

Puede que esta no sea la mejor forma eficaz. Pero intenta

union(
summarize(table, table[project Number] , table[project Name], table[phase], table[Current], table[funded]),
summarize(table, table[project Number] , table[project Name], table[phase2], table[Current2], table[funded2]),
summarize(table, table[project Number] , table[project Name], table[phase3], table[Current3], table[funded3]),
summarize(table, table[project Number] , table[project Name], table[phase4], table[Current4], table[funded4])
)

Aprecie sus felicitaciones. En caso de que esta sea la solución que está buscando, márquela como la Solución. En caso de que no sirva de ayuda, proporcione información adicional y márqueme con @
Gracias. Mi blog reciente –
Winner-Topper-on-Map-How-to-Coloring-States-on-a-Map-with-Winners, HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Trabajando-con-períodos-de-tiempo-no-estándar-y-comparación-de-datos-en-rangos-de-fechas

Conectarse en Linkedin

Entradas relacionadas

Deja un comentario Cancelar respuesta