Sample Data.xlsx (16.4 KB)
In the attached file, I have multiple cash and credit columns. I want to transform the data so that I can obtain only one cash and credit column in Sheet 2.
You’ll want to unpivot the “Item” columns (Item E* and Item S*). Then use conditional columns to assign the Cash and Credit values based on whether the item is from the E series (using the Cash and Credit columns) or the S series (using the Cash_1 and Credit_2 columns). That will yield a long-form table where each row corresponds to a specific item, with the correct Cash, Credit, and Quantity values aligned:
let
Source = Excel.Workbook(File.Contents("C:\Users\HufferD\Downloads\Sample Data.xlsx"), null, true),
#"Sample Data_Sheet" = Source{[Item="Sample Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Sample Data_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
{"Column1", type any}, {"Column2", type text}, {"Column3", type text},
{"Item E1", Int64.Type}, {"Item E2", Int64.Type}, {"Item E3", Int64.Type}, {"Item E4", Int64.Type},
{"CASH", Int64.Type}, {"CREDIT", Int64.Type}, {"Item S1", Int64.Type}, {"Item S2", Int64.Type},
{"Item S3", Int64.Type}, {"CASH_1", Int64.Type}, {"CREDIT_2", Int64.Type}
}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{
{"Column1", "Date"}, {"Column2", "Salesman Name"}, {"Column3", "Branch"}
}),
#"Removed Top Rows" = Table.Skip(#"Renamed Columns",1),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Top Rows",{{"Date", type date}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {
"Date", "Salesman Name", "Branch", "CASH", "CREDIT", "CASH_1", "CREDIT_2"
}, "Attribute", "Value"),
#"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Attribute", "Attribute - Copy"),
#"Extracted Text Range" = Table.TransformColumns(#"Duplicated Column", {
{"Attribute - Copy", each Text.Middle(_, 5, 1), type text}
}),
// Add a conditional column to assign Cash values based on whether the item is 'E' or 'S'
#"Added Conditional Column" = Table.AddColumn(#"Extracted Text Range", "Cash",
each if [#"Attribute - Copy"] = "E" then [CASH] else if [#"Attribute - Copy"] = "S" then [CASH_1] else null
),
// Add a conditional column to assign Credit values based on whether the item is 'E' or 'S'
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Credit",
each if [#"Attribute - Copy"] = "E" then [CREDIT] else if [#"Attribute - Copy"] = "S" then [CREDIT_2] else null
),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Attribute - Copy"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "Item"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"CASH", "CREDIT", "CASH_1", "CREDIT_2"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns1",{{"Value", "Quantity"}})
in
#"Renamed Columns2"
I realize I forgot to share an image of the transformed data:
Hopefully that’s what you were hoping to achieve.