How to create nested Table appended from row valus / column data

[Radoslaw Poprawski]

Hello.
I need to add a new column which would contain a nested Table in which:
Column1 contains: Reporting from as 1st row and Reporting to as second row
Column2 contains Amount from as 1st row and Amount to as 2nd row,

Here is the link to the file.

Case book.xlsx

Microsoft Excel Workbook

1drv.ms

 

[Radoslaw Poprawski]

up up up

 

[XL Pro]

If I'm understanding your requirements correctly, is this what you need:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reporting code", type text}, {"Reporting from", type text}, {"Amount", Int64.Type}, {"Reporting to", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Amount]*-1),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom1", each Table.FromColumns({#"Added Custom"[Reporting from] & #"Added Custom"[Reporting to],#"Added Custom"[Amount] & #"Added Custom"[Custom]},{"Reporting","Amount"})),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Amount", "Amount from"}, {"Custom", "Amiunt to"}})
in
    #"Renamed Columns"

 

[Radoslaw Poprawski]

Hi,
Thank you.
thats one solution to my problem.
But I was thinking something more of Row related. meaing that created Table in row number 1 contains only data from row row 1 split into 2 rows in nested table.

 

[XL Pro]

Ah ok, how about this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reporting code", type text}, {"Reporting from", type text}, {"Amount", Int64.Type}, {"Reporting to", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Amount]*-1),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom1", each Table.FromColumns({{Record.Field(_,"Reporting from"), Record.Field(_,"Reporting to")},{Record.Field(_,"Amount"), Record.Field(_,"Custom")}},{"Reporting","Amount"})),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Amount", "Amount from"}, {"Custom", "Amount to"}})
in
    #"Renamed Columns"

 

[XL Pro]

Looks like I made the nested table more complicated than I needed to, here's an update:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reporting code", type text}, {"Reporting from", type text}, {"Amount", Int64.Type}, {"Reporting to", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Amount", "Amount from"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Amount to", each [Amount from]*-1),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Table.FromColumns({{[Reporting from], [Reporting to]},{[Amount from], [Amount to]}},{"Reporting","Amount"}))
in
    #"Added Custom1"

 

[Radoslaw Poprawski]

Thats it!
Thank you.
I know now where I did mistake with Table.FromColumns
it was square brackets. i used {{}}{{}} like this

Thank you.