I'm working on a simplification of some of my reports by using Power Query. I have a table with this structure:

In the first column we can see the Sales Order Number. In the second column we can see the Message type. If it is a XR or PR Message. And in the last column we can find the Key for the status:

• A = Active
• B = Active
• C = Closed

With this logic I would like to get this result table:

This way I can see as a cross table the type and the state per sales order. How is this possible by using Power Query?

CodePudding user response：

Are you sure about your results for Sales Order 103?

Try the following M code, amending where necessary (Source step, for example):

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  XRCol = Table.AddColumn(
    Source, 
    "XR", 
    each Text.BeforeDelimiter([Message], "-") = "XR", 
    type logical
  ), 
  PRCol = Table.AddColumn(
    XRCol, 
    "PR", 
    each Text.BeforeDelimiter([Message], "-") = "PR", 
    type logical
  ), 
  XRActCol = Table.AddColumn(PRCol, "XR Active", each [XR] and [Key] <> "C", type logical), 
  PRActCol = Table.AddColumn(XRActCol, "PR Active", each [PR] and [Key] <> "C", type logical), 
  RemoveCols = Table.RemoveColumns(PRActCol, {"Message", "Key"}), 
  Group = Table.Group(
    RemoveCols, 
    {"Sales Order"}, 
    {
      {"XR", each List.Max([XR]), type logical}, 
      {"PR", each List.Max([PR]), type logical}, 
      {"XR Active", each List.Max([XR Active]), type logical}, 
      {"PR Active", each List.Max([PR Active]), type logical}
    }
  )
in
  Group

CodePudding user response：

If I see it correctly your output PR and XR is mixed up in Sales order 103.

Try if this works for you:

let
    Source = Excel.Workbook(File.Contents("C:\Users\maitting\Documents\Mappe1.xlsx"), null, true),
    Tabelle1_Sheet = Source{[Item="Tabelle1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Tabelle1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sales Order", Int64.Type}, {"Message", type text}, {"Key", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Message", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Message"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Message", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","A","Active",Replacer.ReplaceText,{"Key"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","B","Active",Replacer.ReplaceText,{"Key"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","C","Closed",Replacer.ReplaceText,{"Key"}),
    #"Merged Columns" = Table.CombineColumns(#"Replaced Value2",{"Message", "Key"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns", {"Sales Order"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Value]), "Value", "Attribute", List.Count),
    #"Added Conditional Column" = Table.AddColumn(#"Pivoted Column", "XR", 
        each if [#"XR - Active"] = 1 then 1 
        else if [#"XR - Closed"] = 1 then 1 
        else 0),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "PR", 
        each if [#"PR - Active"] = 1 then 1 
        else if [#"PR - Closed"] = 1 then 1 
        else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"XR - Closed", "PR - Closed"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Sales Order", "XR", "PR", "XR - Active", "PR - Active"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"XR", type logical}, {"PR", type logical}, {"XR - Active", type logical}, {"PR - Active", type logical}})
in
    #"Changed Type2"

Output:

I've just added Sales order 104 with PR Closed to handle all possible cases.

CodePudding user response：

My version

let Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
RemoveEndingColumn = Table.TransformColumns(Source,{{"Message", each Text.BeforeDelimiter(_, "-"), type text}}),
#"Create MessageKey" = Table.AddColumn(RemoveEndingColumn, "MessageKey", each [Message]&" "&[Key]),
StackColumns = Table.RenameColumns(Table.SelectColumns(#"Create MessageKey",{"Sales Order", "Message"}),{{"Message", "Column"}})& Table.RenameColumns(Table.SelectColumns(#"Create MessageKey",{"Sales Order", "MessageKey"}),{{"MessageKey", "Column"}}),
#"Added Custom1" = Table.AddColumn(StackColumns, "TrueFalse", each true, type logical),
#"Pivoted Column" = Table.Pivot(#"Added Custom1", List.Distinct(#"Added Custom1"[Column]), "Column", "TrueFalse")
in #"Pivoted Column"