Forum Discussion
Solved
Split one column (mixed titles/data) into two columns
Hi all, I'm new to PowerQuery and PowerBI, and I've been given a task to set up some data for PowerBI (see image below, first column is our data, second and third are what we need to do with it)....
Hi JB2020,
You could try below M code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9QitWJVnL2MTQyMcLKNAEzXYNcEUKm6MzgcEcXN4SgGVamKbqZjs6uQIAQNUdREAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [column1 = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.StartsWith([column1],"CL") then [column1] else null), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if Text.StartsWith([column1],"CL") then null else [column1]), #"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom.1"}) in #"Filled Down"Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi JB2020,
You could try below M code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9QitWJVnL2MTQyMcLKNAEzXYNcEUKm6MzgcEcXN4SgGVamKbqZjs6uQIAQNUdREAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [column1 = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.StartsWith([column1],"CL") then [column1] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if Text.StartsWith([column1],"CL") then null else [column1]),
#"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom.1"})
in
#"Filled Down"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, your solution works perfectly for the data we've presented! Unfortunately they don't all start with CL - I've marked it as solved for anyone who needs a similar solution, but this is what my manager came up with which also works:
[FILEPATH] being the actual filepath of course:
let
Source = Excel.Workbook(File.Contents("[FILEPATH]"), null, true),
Financial_Statement_Generator_0_Sheet = Source{[Item="Financial_Statement_Generator_0",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Financial_Statement_Generator_0_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"iPower", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",7),
#"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Operating Unit ", type text}, {" TY", type any}, {" PL", type any}, {" var to PL", type any}, {" LY", type any}, {" var to LY", type any}, {" TY_1", type any}, {" PL", type any}, {" var to PL", type any}, {" LY", type any}, {" var to LY", type any}}),
#"Removed Top Rows1" = Table.Skip(#"Changed Type1",1),
#"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows1",{{"Operating Unit ", "OU"}}),
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Custom", each if Text.Contains([OU], "MARKET SCALE") then "MARKET SCALE" else if Text.Contains([OU], "PLANNED VARIANCE") then "PLANNED VARIANCE" else if Text.Contains([OU], "ACTUAL VARIANCE") then "ACTUAL VARIANCE" else if Text.Contains([OU], "DISBURSEMENTS") then "DISBURSEMENTS" else if Text.Contains([OU], "OTHER REVENUE") then "OTHER REVENUE" else if Text.Contains([OU], "AR PROV / WOFFS") then "AR PROV / WOFFS" else if Text.Contains([OU], " LESS: DISBURSEMENTS") then " LESS: DISBURSEMENTS" else if Text.Contains([OU], "NET REVENUE") then "NET REVENUE" else if Text.Contains([OU], " RESOURCE COSTS") then " RESOURCE COSTS" else if Text.Contains([OU], "ENGAGEMENT MARGIN") then "ENGAGEMENT MARGIN" else if Text.Contains([OU], " INCOME from RESOURCE") then " INCOME from RESOURCE" else if Text.Contains([OU], "CHARGEABLE HOURS") then "CHARGEABLE HOURS" else null),
#"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([OU] <> " LESS: DISBURSEMENTS" and [OU] <> " INCOME from RESOURCE" and [OU] <> " RESOURCE COSTS" and [OU] <> "ACTUAL VARIANCE" and [OU] <> "AR PROV / WOFFS" and [OU] <> "CHARGEABLE HOURS" and [OU] <> "DISBURSEMENTS" and [OU] <> "ENGAGEMENT MARGIN" and [OU] <> "MARKET SCALE" and [OU] <> "NET REVENUE" and [OU] <> "OTHER REVENUE" and [OU] <> "PLANNED VARIANCE")),
#"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows",{{" TY", "Month TY"}, {" PL", "Month PL"}, {" var to PL", "Month Var to PL"}, {" LY", "Month LY"}, {" var to LY", "Month Var to LY"}, {" TY_1", "YTD TY"}, {" PL", "YTD PL"}, {" var to PL", "YTD Var to PL"}, {" LY", "YTD LY"}, {" var to LY", "YTD Var to LY"}, {"Custom", "Line Description"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Month TY", type number}, {"Month PL", type number}, {"Month Var to PL", type number}, {"Month LY", type number}, {"Month Var to LY", type number}, {"YTD TY", type number}, {"YTD PL", type number}, {"YTD Var to PL", type number}, {"YTD LY", type number}, {"YTD Var to LY", type number}})
in
#"Changed Type2"