Search code examples
excelformattingoffice365powerquerydelimiter

Arranging values in a specific order using power query

I wish to clean up a table I have been working on removing complex formulas and opting to sort my data using PQE.

Below is a made up example of the data I am working with. enter image description here

I wish to have the output column alternate between material and sub values akin to headers and sub headers in a book.

M Code so far:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Material ", type text}, {"Sub ", type text}, {"CAS", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Material ", Text.Trim, type text}, {"Sub ", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Sub ", Text.Clean, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Cleaned Text","-","",Replacer.ReplaceText,{"Sub "}),
    #"Merged Columns" = Table.CombineColumns(#"Replaced Value",{"Material ", "Sub "},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Merged", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged", type text}})
in
    #"Changed Type1"

The problem with this code is that it doesn't properly list the materials when combined and I also don't know how to get the CAS numbers to be correctly sorted.

If anyone has any thoughts how to achieve the desired output it would be appreciated.

Solution

  • You can get your output from your input using the same technique I showed you in your last, similar question.

    • create two lists of the columns; then List.Zip to combine them.
    • The trick is that for List 1, (eg output column 1), you may need to add the contents of the Material column at the top of the Sub column list (or replace the - if that's all there is; and, if that is the case, add a - at the start of the CAS list; so things will line up at the end.

    M Code

    let
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", type text}, {"Sub", type text}, {"CAS", type text}}),

    //combine the columns
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each
        let 
            L1 = if [Sub] = "-" then {[Material]}
                    else List.Combine({{[Material]},Text.Split([Sub],"#(lf)")}),
            L2 = if [Sub] = "-" then {[CAS]}
                    else List.Combine({{"-"},Text.Split([CAS],"#(lf)")})
        in 
            List.Zip({L1,L2})),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Material", "Sub", "CAS"}),

    //split the combined columns
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns1", "Custom"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", 
        {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", 
        "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Material", "CAS"})
in
    #"Split Column by Delimiter"

    enter image description here