Extract values and put them in other columns

[md8422]

Hello Everyone!

I'm bumping my head on this issue I can't really solve.

I have some data in a txt file with this format and I would like to extract certain values and put them in other columns, here an example:

Any idea?

Thank you All

[ronrsnfld · Accepted solution]

Assuming your original data is as you show; with four items per product and all in the same order, I suggest:

• Add a column which is the type of information (to become the column headers in your final output)
• Then Pivot with no aggregation
  • That option in the UI won't work, so I have provided a custom function to handle these instances where there are multiple items per information type
  • Add this custom function into a blank query, and rename it something:  I renamed it fnPivotAll

Custom function: Rename fnPivotAll

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

//Rename:  fnPivotAll

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

Main Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    //add column describing the types of data
//assuming all in same order, and groups of 4
//these will become the column headers
    numProducts = Table.RowCount(Source) /4,
    newCols= List.Repeat({"Product Name","Product Description","Product Price","Product Discount"},numProducts),
    tbl = Table.FromColumns(
        Table.ToColumns(Source) & {newCols}
    ),

//Pivot with no aggregation and set data types
    pivot = fnPivotAll(tbl,"Column2","Column1"),
    #"Changed Type" = Table.TransformColumnTypes(pivot,{{"Product Name", type text}, {"Product Description", type text}, {"Product Price", type text}, {"Product Discount", type text}})
in
    #"Changed Type"

[MarkLaf · Accepted solution]

Another way to tackle this is to

1. add a field index column assuming 4 fields repeated in same order for each product
2. group by field index to get the full list of values per field - note that sort + GroupKind.Local should be used to preserve value order
3. use the list of values to construct the desired table

let
    Source = Table,

    //Assuming four fields repeated in same order for each product, creating a field index with AddIndex and Mod
    #"Add Original Index" = Table.AddIndexColumn(Source, "Original Index", 0, 1, Int64.Type),
    #"Add Field Index" = 
    Table.AddColumn(
        #"Add Original Index",
        "Field Index", 
        each Number.Mod([Original Index] ,4)+1, 
        Int64.Type
    ),

    //sorting and grouping using GroupKind.Local are required to ensure order of field values is preserved
    #"Sort by Field then Original" = 
    Table.Sort(
        #"Add Field Index",
        {{"Field Index", Order.Ascending}, {"Original Index", Order.Ascending}}
    ),
    #"Group on Field Index" = 
    Table.Group(
        #"Sort by Field then Original", 
        {"Field Index"}, 
        {{"FieldVals", each _[Column], type list}},
        GroupKind.Local
    ),

    //take grouped values and covert to a table with desired column names
    #"Field Values to Table" = 
    Table.FromColumns( 
        #"Group on Field Index"[FieldVals] , 
        {"Product Name","Product Description","Product Price","Product Discount"}
    )
in
    #"Field Values to Table"

All that said, the best practice solution would be to get a field column in your source data so you can do a straight pivot.

[ronrsnfld · Accepted solution]

Assuming your original data is as you show; with four items per product and all in the same order, I suggest:

• Add a column which is the type of information (to become the column headers in your final output)
• Then Pivot with no aggregation
  • That option in the UI won't work, so I have provided a custom function to handle these instances where there are multiple items per information type
  • Add this custom function into a blank query, and rename it something:  I renamed it fnPivotAll

Custom function: Rename fnPivotAll

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

//Rename:  fnPivotAll

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

Main Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    //add column describing the types of data
//assuming all in same order, and groups of 4
//these will become the column headers
    numProducts = Table.RowCount(Source) /4,
    newCols= List.Repeat({"Product Name","Product Description","Product Price","Product Discount"},numProducts),
    tbl = Table.FromColumns(
        Table.ToColumns(Source) & {newCols}
    ),

//Pivot with no aggregation and set data types
    pivot = fnPivotAll(tbl,"Column2","Column1"),
    #"Changed Type" = Table.TransformColumnTypes(pivot,{{"Product Name", type text}, {"Product Description", type text}, {"Product Price", type text}, {"Product Discount", type text}})
in
    #"Changed Type"