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

RSN 1 Reputation point
2021-08-31T20:16:37.743+00:00

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

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
37,174 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Miguel Caballero Sierra 171 Reputation points MVP
    2021-09-01T13:05:43.787+00:00

    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:

    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

    0 comments No comments

  2. RSN 1 Reputation point
    2021-09-01T14:33:48.56+00:00

    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"