how to use power query to transform data

[Oscarr]

hi All

how to use power query to transform data like this?

 

[GraH - Guido]

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 4), type number),
    #"Added Custom" = Table.AddColumn(#"Inserted Modulo", "Reference", each if [name] = null then null else [Index]),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Reference"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Reference"}, {{"Sub", each _, type table [deposit date=any, pay date=any, name=nullable text, brand=any, detail=text, price1=nullable text, price2=nullable number, deposit=nullable number, Index=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Attributes", each [Deposit Date =
 let
 DateParts = List.RemoveNulls([Sub][deposit date]),
 AddParts = Number.From(DateParts{0})+Number.From(DateParts{1}),
 Date = DateTime.From(AddParts)
 in
 Date,
 Pay Date =
 let
 DateParts = List.RemoveNulls([Sub][pay date]),
 AddParts = Number.From(DateParts{0})+Number.From(DateParts{1}),
 Date = DateTime.From(AddParts)
 in
 Date,
 Table = Table.SelectRows([Sub], each [Modulo] =1),
 Brands = [Sub][brand],
 Details = [Sub][detail]
  ]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Sub"}),
    #"Expanded Attributes" = Table.ExpandRecordColumn(#"Removed Columns1", "Attributes", {"Deposit Date", "Pay Date", "Table", "Brands", "Details"}, {"Deposit Date", "Pay Date", "Table", "Brands", "Details"}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Expanded Attributes", "Table", {"name", "price1", "price2", "deposit"}, {"name", "price1", "price2", "deposit"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Table", {"Brands", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Details", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values1", "Brands", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Brands.1", "Brands.2", "Brands.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Brands.1", type text}, {"Brands.2", Int64.Type}, {"Brands.3", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Details", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Details.1", "Details.2", "Details.3", "Details.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Details.1", type text}, {"Details.2", type text}, {"Details.3", type text}, {"Details.4", type text}, {"Deposit Date", type datetime}, {"Pay Date", type datetime}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type1",{"Reference"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns2",{"Deposit Date", "Pay Date", "name", "Brands.1", "Brands.2", "Brands.3", "Details.1", "Details.2", "Details.3", "Details.4", "price1", "price2", "deposit"})
in
    #"Reordered Columns"

 

[p45cal]

Cell A10 of Sheet2 of the attached. It's ugly.

 

[vletm]

Oscarr
... or something ... different ...
#1 Add needed data to Sheet1
#2 Select Sheet2 to see expected results

 

[Oscarr]

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 4), type number),
    #"Added Custom" = Table.AddColumn(#"Inserted Modulo", "Reference", each if [name] = null then null else [Index]),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Reference"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Reference"}, {{"Sub", each _, type table [deposit date=any, pay date=any, name=nullable text, brand=any, detail=text, price1=nullable text, price2=nullable number, deposit=nullable number, Index=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Attributes", each [Deposit Date =
let
DateParts = List.RemoveNulls([Sub][deposit date]),
AddParts = Number.From(DateParts{0})+Number.From(DateParts{1}),
Date = DateTime.From(AddParts)
in
Date,
Pay Date =
let
DateParts = List.RemoveNulls([Sub][pay date]),
AddParts = Number.From(DateParts{0})+Number.From(DateParts{1}),
Date = DateTime.From(AddParts)
in
Date,
Table = Table.SelectRows([Sub], each [Modulo] =1),
Brands = [Sub][brand],
Details = [Sub][detail]
  ]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Sub"}),
    #"Expanded Attributes" = Table.ExpandRecordColumn(#"Removed Columns1", "Attributes", {"Deposit Date", "Pay Date", "Table", "Brands", "Details"}, {"Deposit Date", "Pay Date", "Table", "Brands", "Details"}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Expanded Attributes", "Table", {"name", "price1", "price2", "deposit"}, {"name", "price1", "price2", "deposit"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Table", {"Brands", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Details", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values1", "Brands", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Brands.1", "Brands.2", "Brands.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Brands.1", type text}, {"Brands.2", Int64.Type}, {"Brands.3", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Details", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Details.1", "Details.2", "Details.3", "Details.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Details.1", type text}, {"Details.2", type text}, {"Details.3", type text}, {"Details.4", type text}, {"Deposit Date", type datetime}, {"Pay Date", type datetime}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type1",{"Reference"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns2",{"Deposit Date", "Pay Date", "name", "Brands.1", "Brands.2", "Brands.3", "Details.1", "Details.2", "Details.3", "Details.4", "price1", "price2", "deposit"})
in
    #"Reordered Columns"

thanks for help

 

[Oscarr]

Cell A10 of Sheet2 of the attached. It's ugly.

thanks for help