question

RajenderSingh-0532 avatar image
0 Votes"
RajenderSingh-0532 asked MiguelCaballeroSierra-6578 edited

How to combine multiple rows data in one cell with additional Text

Hi All,

I have excel file and 2 sheets contains Input and Output :-
Can somebody tell me how to arrange input sheet data to output sheet data ?

Thanks in advance for valuable support.


127979-output.jpg

128007-input1.jpg



power-query-not-supported
output.jpg (50.8 KiB)
input1.jpg (37.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MiguelCaballeroSierra-6578 avatar image
0 Votes"
MiguelCaballeroSierra-6578 answered

Hi @RajenderSingh-0532 ,

Copie and paste the following M conde in a Blank new query ->

Start of the M Code:


let

 Origen = 
 Table.FromRows ( 
     Json.Document (
         Binary.Decompress ( 
             Binary.FromText (
                 "bc49C8IwEAbgvxIyt5jLZ+vsImQo6JZkkg51iEWy+O815w2B5l3e4+AeLgQONXzgy/v1XB+FXS9ntoxCOtAmZr/lFVd+FDUQ820r/xVexlw++8p+fa8teRoClzUdc7Kgj6ZsTLwkU5Gp0VQ1XdNC51HVoHhKqCHUIjoLJ/qPCns0NTsxj5NpdDRId6RP1DM1CJ7SFw==", 
                 BinaryEncoding.Base64
             ), 
             Compression.Deflate
         )
     ), 
     let 
         _t = 
         (
             ( type nullable text ) 
             meta 
             [ Serialized.Text = true ]
         ) 
     in 
         type table 
         [ #"TM Cell ID" = _t, Output = _t] 
 ),

 #"Personalizada agregada" = 
 Table.AddColumn ( 
     Origen, 
     "Personalizado", 
     each Lines.FromText ( [Output] )
 ),

 #"Se expandió Personalizado" = 
 Table.ExpandListColumn ( 
     #"Personalizada agregada", 
     "Personalizado"
 ),

 #"Índice agregado" = 
 Table.AddIndexColumn ( 
     #"Se expandió Personalizado", 
     "Índice", 
     1, 
     1, 
     Int64.Type
 ),

 #"Columnas quitadas" = 
 Table.RemoveColumns ( 
     #"Índice agregado", {"Output"} 
 ),
    
 #"Personalizada agregada1" = 
 Table.AddColumn ( 
     #"Columnas quitadas", 
     "Personalizado.1", 
     each 
     #"Columnas quitadas"[Personalizado]{ [Índice] }?
 ),
 #"Personalizada agregada2" = 
 Table.AddColumn ( 
     #"Personalizada agregada1", 
     "Personalizado.2", 
     each [ [Personalizado], [Índice], [Personalizado.1] ]
 ),

 #"Dividir columna por delimitador" = 
 Table.SplitColumn ( 
     #"Personalizada agregada2", 
     "Personalizado.2",
     // ( ItemActual as any ) as list => ...
     each 
     if
         Text.Contains ( _[Personalizado] , ":")
     then
         { Text.BeforeDelimiter ( _[Personalizado], ":" ), Text.AfterDelimiter ( _[Personalizado], ":" ) }
     else
         if
            not
            Text.Contains ( _[Personalizado.1], ":" )
         then
             { "Price Book Item", { _[Personalizado] , _[Personalizado.1] } }
         else
             { null,  null },
     { "Primera", "Segunda" } 
 ),

 #"Errores reemplazados" = 
 Table.ReplaceErrorValues ( #"Dividir columna por delimitador", { {"Primera", null } }),

 #"Errores reemplazados1" = 
 Table.ReplaceErrorValues ( 
     #"Errores reemplazados", 
     { {"Segunda", null } }
 ),

 #"Columnas quitadas1" = 
 Table.RemoveColumns ( 
     #"Errores reemplazados1", 
     { 
         "Personalizado", 
         "Índice", 
         "Personalizado.1"
     }
 ),
    
 #"Filas filtradas" = 
 Table.SelectRows ( 
     #"Columnas quitadas1", 
     each 
     ( [Primera] <> null )
 ),
    
 #"Filas inferiores quitadas" = 
 Table.RemoveLastN ( #"Filas filtradas", 0 ),

 Desapliado = 
 Table.FromRows ( 
     List.Split ( 
         #"Filas inferiores quitadas"[Segunda], 
         4 
     )
 ),

 #"Errores quitados" = 
 Table.RemoveRowsWithErrors ( 
     Desapliado, 
     { "Column1" } 
 ),

 #"Se expandió Column4" = 
 Table.ExpandListColumn ( 
     #"Errores quitados", 
     "Column4"
 ),

 #"Índice agregado1" = 
 Table.AddIndexColumn ( 
     #"Se expandió Column4", 
     "Índice", 
     1, 
     1, 
     Int64.Type
 ),
    
 #"Personalizada agregada3" = 
 Table.AddColumn ( 
     #"Índice agregado1", 
     "Personalizado", 
     each 
     if 
         Number.IsOdd ( [Índice] ) 
     then
         [ 
             Project = null, 
             Line Item ID = null,
             TM Cell ID = null,
             Price Book Item = [Column4] 
         ] 
     else 
         [ 
             Project = [Column1], 
             Line Item ID = [Column2],
             TM Cell ID = [Column3],
             Price Book Item = [Column4] 
         ]  
 ),

 #"Otras columnas quitadas" = 
 Table.SelectColumns ( 
     #"Personalizada agregada3",
     {"Personalizado"}
 ),
    
 #"Se expandió Personalizado1" = 
 Table.ExpandRecordColumn ( 
     #"Otras columnas quitadas", 
     "Personalizado", 
     { 
         "Project", 
         "Line Item ID", 
         "TM Cell ID", 
         "Price Book Item"
     }, 
     {
         "Project", 
         "Line Item ID", 
         "TM Cell ID", 
         "Price Book Item"
     }
 ),

 #"Columnas reordenadas" = 
 Table.ReorderColumns ( 
     #"Se expandió Personalizado1", 
     {
         "Line Item ID", 
         "TM Cell ID", 
         "Project", 
         "Price Book Item"
     }
 )

in
#"Columnas reordenadas"


End Of The M Code


The input table in the code is:

128189-input.png


And the result is:

128324-output.png

You can use the solution as a template, but keep in mind that the input need tobe the same structure: same columnas,same columns name as in the the picture above that show the input in this comment.

the necessary steps are deployed (render) in the interface for yout analysis once you paste the M code in a blank query.

P.D: Sorry for the language in the steps (Spanish) I forgot to change to my version of Power BI/Power Query in english.

Best
— Miguel Caballero



input.png (25.4 KiB)
output.png (21.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RajenderSingh-0532 avatar image
0 Votes"
RajenderSingh-0532 answered MiguelCaballeroSierra-6578 edited

Hi Miguel,

Thanks a lot for your prompt support. I pasted the given code in Blank Query but its showing Token EOF error in below given line

      [ #"TM Cell ID" = _t, Output = _t] 

),

Also how to change the file/Sheet path ?

As of now my input data is in same excel file in worksheet "Input" and Table name is "Raw Data"







· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@RajenderSingh-0532 to use in yout table

1) Use this version of the solution: https://gist.githubusercontent.com/ExcelFreeBlog/dceeef700d688b4229f2a77f2765a3f2/raw/8ff7e2471d43863b6c70d5c0b1888754259e551c/RajenderSingh-0532-2

Wich is a function that yo

2) Load your table to Power Query (make sure that there are two columns with names: TM Cell ID and Output literally)

3) Use this function,

Best
— Miguel Caballero

0 Votes 0 ·