Convierta filas en columna como se muestra a continuación

[Haleem]

Tengo la primera tabla, que necesito mostrar en el formato que se muestra en la tabla 2. En resumen, Atributo, CONT y Exp deben dividirse en como se muestra a continuación. esto es sólo un ejemplo de datos. Tengo cientos de commbination en la columna Cont y Exp junto con desde y hacia códigos.

[Ashish_Mathur · Accepted solution]

Hola

Estoy seguro de que hay una mejor manera, pero este código M funciona en sus datos de muestra al menos

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Source,{"CONT", "EXP"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    Partition = Table.Group(#"Merged Columns", {"Attribute"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"From", "To", "Merged", "Index"}, {"From", "To", "Merged", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Partition", List.Distinct(#"Expanded Partition"[Attribute]), "Attribute", "Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "TO_Connection_DK1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"TO_Connection_DK1.1", "TO_Connection_DK1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"TO_Connection_DK1.1", type text}, {"TO_Connection_DK1.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "FROM_Connection_DK1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"FROM_Connection_DK1.1", "FROM_Connection_DK1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"FROM_Connection_DK1.1", type text}, {"FROM_Connection_DK1.2", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"From", "To", "TO_Connection_DK1.1", "FROM_Connection_DK1.1", "TO_Connection_DK1.2", "FROM_Connection_DK1.2"})
in
    #"Reordered Columns"

Espero que esto ayude.

[Haleem]

@Ashish_Mathur  Thanks a lot. Can you please share the Power Query file which you have made for the is query. I can see you have only posted a picture/screen shot of the power query, it would be great to send me the file. Thanks.

[Ashish_Mathur]

Hola

Ya he compartido el código M contigo. Simplemente copie el código M y péguelo en el Editor de consultas.

[Haleem]

@Ashish_Mathur  I already have the below code in the my query editor. How can I add your code here? I just copied your code where my code is ended, but it gives me an error. 

let
Source = Excel.Workbook(File.Contents("C:\Users\dksahs\OneDrive - Chr Hansen\Documents\Personal\Usman\3,0\Master_28-10-2020.xlsx"), null, true),
MasterData_tbl_Table = Source{[Item="MasterData_tbl",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(MasterData_tbl_Table,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"DK1_FROM", type text}, {"FROM_Connections_DK1", type text}, {"DK1_TO", type text}, {"TO_Connection_DK1", type text}, {"DK2_FROM", type text}, {"FROM_Connection_DK2", type text}, {"DK2_TO", type text}, {"TO_Connection_DK2", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "DK1_FROM", "FROM_Connections_DK1", "DK1_TO", "TO_Connection_DK1", "DK2_FROM", "FROM_Connection_DK2", "DK2_TO", "TO_Connection_DK2"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"DK2_FROM", "FROM_Connection_DK2", "DK2_TO", "TO_Connection_DK2"}),
#"Inserted Merged Column" = Table.AddColumn(#"Removed Columns", "Merged", each Text.Combine({[DK1_FROM], [FROM_Connections_DK1], [DK1_TO], [TO_Connection_DK1]}, "-"), type text),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Merged", "KEY_DK1"}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"Index", "KEY_DK1", "DK1_FROM", "FROM_Connections_DK1", "DK1_TO", "TO_Connection_DK1"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns1", each ([Index] = 7)),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Filtered Rows", {"FROM_Connections_DK1", "TO_Connection_DK1"}, "Attribute", "Value"),
#"Merged Queries" = Table.NestedJoin(#"Unpivoted Only Selected Columns", {"Value"}, Connections1, {"Connections"}, "Connections1", JoinKind.LeftOuter),
#"Expanded Connections1" = Table.ExpandTableColumn(#"Merged Queries", "Connections1", {"Ex-1", "Ex-2", "Ex-3", "Ex-4", "Ex-5", "Ex-6", "Ex-7", "Ex-8", "Ex-9", "Ex-10", "Ex-11", "Ex-12", "Ex-13", "Ex-14", "Ex-15", "Ex-16", "Ex-17", "Ex-18", "Ex-19", "Ex-20", "Ex-21", "Ex-22", "Ex-23", "Ex-24", "Ex-25", "Ex-26", "Ex-27", "Ex-28", "Ex-29", "Ex-30", "Ex-31", "Ex-32", "Ex-33", "Ex-34", "Ex-35", "Ex-36", "Ex-37", "Ex-38", "Ex-39", "Ex-40", "Ex-41", "Ex-42", "Ex-43", "Ex-44", "Ex-45", "Ex-46", "Ex-47", "Ex-48", "Ex-49", "Ex-50", "Ex-51", "Ex-52", "Ex-53", "Ex-54", "Ex-55", "Ex-56", "Ex-57", "Ex-58", "Ex-59", "Ex-60", "Ex-61", "Ex-62", "Ex-63", "Ex-64", "Ex-65", "Ex-66", "Ex-67", "Ex-68", "Ex-69", "Ex-70", "Ex-71", "Ex-72", "Ex-73", "Ex-74", "Ex-75", "Ex-76"}, {"Connections1.Ex-1", "Connections1.Ex-2", "Connections1.Ex-3", "Connections1.Ex-4", "Connections1.Ex-5", "Connections1.Ex-6", "Connections1.Ex-7", "Connections1.Ex-8", "Connections1.Ex-9", "Connections1.Ex-10", "Connections1.Ex-11", "Connections1.Ex-12", "Connections1.Ex-13", "Connections1.Ex-14", "Connections1.Ex-15", "Connections1.Ex-16", "Connections1.Ex-17", "Connections1.Ex-18", "Connections1.Ex-19", "Connections1.Ex-20", "Connections1.Ex-21", "Connections1.Ex-22", "Connections1.Ex-23", "Connections1.Ex-24", "Connections1.Ex-25", "Connections1.Ex-26", "Connections1.Ex-27", "Connections1.Ex-28", "Connections1.Ex-29", "Connections1.Ex-30", "Connections1.Ex-31", "Connections1.Ex-32", "Connections1.Ex-33", "Connections1.Ex-34", "Connections1.Ex-35", "Connections1.Ex-36", "Connections1.Ex-37", "Connections1.Ex-38", "Connections1.Ex-39", "Connections1.Ex-40", "Connections1.Ex-41", "Connections1.Ex-42", "Connections1.Ex-43", "Connections1.Ex-44", "Connections1.Ex-45", "Connections1.Ex-46", "Connections1.Ex-47", "Connections1.Ex-48", "Connections1.Ex-49", "Connections1.Ex-50", "Connections1.Ex-51", "Connections1.Ex-52", "Connections1.Ex-53", "Connections1.Ex-54", "Connections1.Ex-55", "Connections1.Ex-56", "Connections1.Ex-57", "Connections1.Ex-58", "Connections1.Ex-59", "Connections1.Ex-60", "Connections1.Ex-61", "Connections1.Ex-62", "Connections1.Ex-63", "Connections1.Ex-64", "Connections1.Ex-65", "Connections1.Ex-66", "Connections1.Ex-67", "Connections1.Ex-68", "Connections1.Ex-69", "Connections1.Ex-70", "Connections1.Ex-71", "Connections1.Ex-72", "Connections1.Ex-73", "Connections1.Ex-74", "Connections1.Ex-75", "Connections1.Ex-76"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Connections1", {"Index", "KEY_DK1", "DK1_FROM", "DK1_TO", "Attribute", "Value"}, "Attribute.1", "Value.1"),
#"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute.1", "Index", "KEY_DK1"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"DK1_FROM", "FROM"}, {"DK1_TO", "TO"}, {"Value", "CONT"}, {"Value.1", "EXP"}})
in
#"Renamed Columns1"

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Haleem]

@Ashish_Mathur  I already tried the code and managed to execute, but got only one problem. 

The blank columns just miss match the FROM and TO  as highlighted below... if we can manage to delete the blue area then our data is fixed. 

here is the code 

let
Source = Excel.CurrentWorkbook(){[Name="DK1_tbl"]}[Content],

#"Merged Columns" = Table.CombineColumns(Source,{"CONT", "EXP"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
Partition = Table.Group(#"Merged Columns", {"Attribute"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"From", "To", "Merged", "Index"}, {"From", "To", "Merged", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Partition", List.Distinct(#"Expanded Partition"[Attribute]), "Attribute", "Merged"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "TO_Connection_DK1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"TO_Connection_DK1.1", "TO_Connection_DK1.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"TO_Connection_DK1.1", type text}, {"TO_Connection_DK1.2", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "FROM_Connection_DK1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"FROM_Connection_DK1.1", "FROM_Connection_DK1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"FROM_Connection_DK1.1", type text}, {"FROM_Connection_DK1.2", type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"From", "FROM_Connection_DK1.1", "FROM_Connection_DK1.2", "To", "TO_Connection_DK1.1", "TO_Connection_DK1.2"})
in
#"Reordered Columns"

[Ashish_Mathur]

Hi,

Paste your raw data in the file that i shared with you in the past 15 minutes.

[Haleem]

@Ashish_Mathur here is the screenshot after i pasted data into your file. the same problem of the empty cells

[Haleem]

here is the link to the my file. https://1drv.ms/x/s!An2fh7sujfnkhT1rNZHjJ91QRppa?e=ay945B

please find the table and connection as shown below. 

your file with some of my data goes here, as i cannot paste all of my data due to the 3000 rows limitation in your file.

https://1drv.ms/u/s!An2fh7sujfnkhT7E2cCAOsL0Wb2b?e=sIbZQe

[Ashish_Mathur]

Tendremos que empezar de cero. En la primera publicación, solo compartiste 1 combinación de Desde y Hasta. Comparte múltiples combinaciones From y to y muéstrame el resultado esperado allí. En un conjunto de datos pequeño, muestre el resultado esperado. Asegúrese de que el pequeño conjunto de datos que comparte (y, por lo tanto, muestra el resultado de), puede pegarse simplemente en un archivo de MS Excel.

[Haleem]

@Ashish_Mathur I do not know if the link i shared with you earlier is working proper, if not, please find the link here

https://1drv.ms/x/s!An2fh7sujfnkhT_YYaSE9_-J-ZVt?e=ce1FBp

[Haleem]

@Ashish_Mathur If I understood you correctly, you are looking my data in excel form? Here is the data. 

https://1drv.ms/u/s!An2fh7sujfnkhT95tJXSbXkU72o7?e=0SDvKf

[Haleem]

@Ashish_Mathur did you come up with any idea to resolve the issue?

[Haleem]

Sí, tienes razón, hay más de una combinación. Sí, me gusta que los resultados se carguen simplemente en MS Excel, ya que ya he compartido el archivo con usted.

[Ashish_Mathur]

Hola

Comparte el enlace desde donde puedo descargar tu archivo PBI con tus datos y mi última solución.