Forum Discussion

Einomi's avatar
Einomi
Helper V
3 years ago
Solved

Proper Format Records to columns

Hello,

 

I would need to go from this format

 

 

to this 

 

 

in POWER QUERY

 

I have done some steps but the farest I went, is to have the first three columns with Surname, Father Name, Mother Name and the fourth column is the table of all the children belonging to the parents, but I am not sure how to format it as shown above

 

Thanks

 

 

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    NewStep=#table(List.FirstN(Table.ColumnNames(PreviousStepName),3)&{"CHILD NAME","DOB","YEAR"},List.TransformMany(Table.ToRows(PreviousStepName),each List.Split(List.Skip(_,3),3),(x,y)=>List.FirstN(x,3)&y))

3 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

    NewStep=#table(List.FirstN(Table.ColumnNames(PreviousStepName),3)&{"CHILD NAME","DOB","YEAR"},List.TransformMany(Table.ToRows(PreviousStepName),each List.Split(List.Skip(_,3),3),(x,y)=>List.FirstN(x,3)&y))

    • Einomi's avatar
      Einomi
      Helper V
      3 years ago

      Hi wdx223_Daniel 

       

      Many thanks, seems to work, has to do further tests but we are definitely on the right way.

       

      I am bit confused with the # at the beginning NewStep = #table, what this # refers to please ?

      • v-jingzhang's avatar
        v-jingzhang
        Community Support
        3 years ago

        Hi  Einomi 

         

        # is not an independent character here. #table should be used together to create a table. Please refer to #table - PowerQuery M

        Best Regards,
        Community Support Team _ Jing