¿Cómo puedo apilar varias columnas en una tabla, dos a la vez? (ejemplo en la descripción)

[BBHouston]

Digamos que tengo la siguiente tabla:

Ciudad	Fecha	Producto1	Cantidad1	Producto2	Cantidad2	Producto3	Cantidad3
Dallas	9/11	Madera	45	Trigo	32	Horas	21
Austin	9/12	Horas	31	Madera	20	*en blanco*	en blanco*
Houston	9/13	Trigo	42	Madera	17	*en blanco*	*en blanco*

Quiero crear una tabla que tenga este aspecto:

Ciudad	Fecha	Producto	Nombre del producto	Cantidad
Dallas	9/11	1	Madera	45
Dallas	9/11	2	Trigo	32
Dallas	9/11	3	Horas	21
Austin	9/12	1	Horas	31
Austin	9/12	2	Madera	20
Houston	9/13	1	Trigo	42
Houston	9/13	2	Madera	17

¿Cómo puedo crear una tabla como esta con DAX/power BI? He estado bastante atascado en él.

[v-deddai1-msft · Accepted solution]

Hola @BBHouston ,

Consulte la consulta M a continuación:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcknMyUksVtJRstQ3NARS4fn5KUDKxBTEzkhNLAHSxkZAwr8oFUgaGSrF6kQrOZYWl2TmQXQhJI2RDDAyABIgBFLukQ9Unw9Vb4xksokRQoehOUxHLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Date = _t, Product1 = _t, Quantity1 = _t, Product2 = _t, Quantity2 = _t, Product3 = _t, Quantity3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Date", type date}, {"Product1", type text}, {"Quantity1", Int64.Type}, {"Product2", type text}, {"Quantity2", Int64.Type}, {"Product3", type text}, {"Quantity3", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Product3"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value", {{"Quantity1", type text}}, "en-US"),{"Product1", "Quantity1"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Quantity2", type text}}, "en-US"),{"Product2", "Quantity2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.1"),
    #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Quantity3", type text}}, "en-US"),{"Product3", "Quantity3"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.2"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns2", {"City", "Date"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value.1", "Product Name"}, {"Value.2", "Quantity"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Product Name] <> "")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"City"}, {{"table", each _, type table [City=nullable text, Date=nullable date, Product Name=nullable text, Quantity=nullable text]}}),
    RankFunction = (table1 as table) as table =>
     let
      AddIndex = Table.AddIndexColumn(table1, "Product", 1, 1)
    in
      AddIndex,
    #"AddedRank" = Table.TransformColumns(#"Grouped Rows", {"table", each RankFunction(_)}),
    #"Expanded table" = Table.ExpandTableColumn(AddedRank, "table", {"Date", "Product Name", "Quantity", "Product"}, {"table.Date", "table.Product Name", "table.Quantity", "table.Product"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded table",{{"table.Product Name", "Product Name"}, {"table.Quantity", "Quantity"}, {"table.Product", "Product"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"City", "table.Date", "Product", "Product Name", "Quantity"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns",{{"table.Date", "Date"}})
    
in
    #"Renamed Columns2"

Si este post ayuda, entonces considera Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

Saludos

Dedmon Dai

[Ashish_Mathur · Accepted solution]

Hola

Este código M funciona

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Date", type datetime}, {"Product1", type text}, {"Quantity1", Int64.Type}, {"Product2", type text}, {"Quantity2", Int64.Type}, {"Product3", type text}, {"Quantity3", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"City", "Date"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Attribute.1]), "Attribute.1", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Date", Order.Ascending}, {"City", Order.Ascending}, {"Attribute.2", Order.Ascending}})
in
    #"Sorted Rows"

Espero que esto ayude.

[v-deddai1-msft · Accepted solution]

Hola @BBHouston ,

Consulte la consulta M a continuación:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcknMyUksVtJRstQ3NARS4fn5KUDKxBTEzkhNLAHSxkZAwr8oFUgaGSrF6kQrOZYWl2TmQXQhJI2RDDAyABIgBFLukQ9Unw9Vb4xksokRQoehOUxHLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Date = _t, Product1 = _t, Quantity1 = _t, Product2 = _t, Quantity2 = _t, Product3 = _t, Quantity3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Date", type date}, {"Product1", type text}, {"Quantity1", Int64.Type}, {"Product2", type text}, {"Quantity2", Int64.Type}, {"Product3", type text}, {"Quantity3", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Product3"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value", {{"Quantity1", type text}}, "en-US"),{"Product1", "Quantity1"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Quantity2", type text}}, "en-US"),{"Product2", "Quantity2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.1"),
    #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Quantity3", type text}}, "en-US"),{"Product3", "Quantity3"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.2"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns2", {"City", "Date"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value.1", "Product Name"}, {"Value.2", "Quantity"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Product Name] <> "")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"City"}, {{"table", each _, type table [City=nullable text, Date=nullable date, Product Name=nullable text, Quantity=nullable text]}}),
    RankFunction = (table1 as table) as table =>
     let
      AddIndex = Table.AddIndexColumn(table1, "Product", 1, 1)
    in
      AddIndex,
    #"AddedRank" = Table.TransformColumns(#"Grouped Rows", {"table", each RankFunction(_)}),
    #"Expanded table" = Table.ExpandTableColumn(AddedRank, "table", {"Date", "Product Name", "Quantity", "Product"}, {"table.Date", "table.Product Name", "table.Quantity", "table.Product"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded table",{{"table.Product Name", "Product Name"}, {"table.Quantity", "Quantity"}, {"table.Product", "Product"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"City", "table.Date", "Product", "Product Name", "Quantity"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns",{{"table.Date", "Date"}})
    
in
    #"Renamed Columns2"

Si este post ayuda, entonces considera Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

Saludos

Dedmon Dai

[andre]

puede utilizar una función Union(). Crear una nueva tabla como esta

newTable ?

var t0 á ROW("City", BLANK(), "Date", BLANK(), "Product Name", blank(), "Product Qty", blank())

var t1 á summarize(oldTable, oldTable[City], oldTable[Date], oldTable[Product1], oldTable[Quantity1])

var t2 á summarize(oldTable, oldTable[City], oldTable[Date], oldTable[Product2], oldTable[Quantity2])

var t3 á summarize(oldTable, oldTable[City], oldTable[Date], oldTable[Product3], oldTable[Quantity3])

filtro de retorno(union(t0,t1, t2, t3), [date]<> blank())

o algo así

[DataInsights]

@BBHouston,

Pruebe la solución Power Query a continuación. El concepto es el siguiente:

1. Combine cada par de columnas (Producto 1 y Cantidad 1, Producto 2 y Cantidad 2, etc.) utilizando un delimitador.

2. Despotable las columnas combinadas.

3. Divida la columna Valor por el delimitador.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcknMyUksVtJRstQ3NARS4fn5KUDKxBTEzkhNLAHSxkZAwr8oFUgaGSrF6kQrOZYWl2TmQXQhJI2RDDAyABIgBFLukQ9Unw9Vb4xksokRQoehOUxHLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Date = _t, Product1 = _t, Quantity1 = _t, Product2 = _t, Quantity2 = _t, Product3 = _t, Quantity3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Date", type date}, {"Product1", type text}, {"Quantity1", Int64.Type}, {"Product2", type text}, {"Quantity2", Int64.Type}, {"Product3", type text}, {"Quantity3", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Quantity1", type text}}, "en-US"),{"Product1", "Quantity1"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"1"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Quantity2", type text}}, "en-US"),{"Product2", "Quantity2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"2"),
    #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Quantity3", type text}}, "en-US"),{"Product3", "Quantity3"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"3"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Columns2", {"City", "Date"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute", "Product"}, {"Value.1", "Product Name"}, {"Value.2", "Quantity"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Product Name] <> ""))
in
    #"Filtered Rows"

Este enlace lo explica en detalle:

https://datachant.com/2019/10/11/guest-story-unpivot-pairs-of-columns/