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

Question

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.

Answer 1

Hi @RSN ,

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:

And the result is:

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

Answer 2

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"