How to transform data that's all in one table but need to create a column based on each break?

[ninos-shiba]

I have a file that looks like this:

Category 1
	Calls Offered	Calls Handled
4/3/2017	3400	2571
4/4/2017	1278	1130
4/5/2017	2397	2390
4/6/2017	2678	2672
4/7/2017	1687	1675
4/10/2017	2034	2012
Category 2
4/3/2017	434	1231
4/4/2017	765	8495
4/5/2017	656	8465
4/6/2017	982	2313
4/7/2017	878	5656
4/10/2017	6665	949

I want to transform this table into something like this:

Date	Calls Offered	Calls Handled	Category
4/3/2017	3400	2571	1
4/4/2017	1278	1130	1
4/5/2017	2397	2390	1
4/6/2017	2678	2672	1
4/7/2017	1687	1675	1
4/10/2017	2034	2012	1
4/3/2017	434	1231	2
4/4/2017	765	8495	2
4/5/2017	656	8465	2
4/6/2017	982	2313	2
4/7/2017	878	5656	2
4/10/2017	6665	949	2

How would I be able to transform that?

[AlB · Accepted solution]

Hi @ninos-shiba 

This should be done in the query editor. Place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY+xDsMgDER/BTFHCjbYwJylWz8gyhApSZeoldIu/fsSBxjoAIf1OLgbRz3Mn/XxOr4KdKdVWnrqRjkM876/1X3b1mNd6nybn8ue5vOW622PBnyC1hmTBMlDRq4gQB9OAWsyooLQxiwFcUUsriSYka8PcrjEU0Zgqs1YJwKXrdbDXE+1yZ0YAO1fcM+U9uAitbmZWAhTGzsGlEJg29RB+tBpbUMzy0/RRT1NPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1], "Category") then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column2] <> " ")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{" ", type date}, {"Calls Offered", Int64.Type}, {"Calls Handled", Int64.Type}, {"Category 1", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{" ", "Date"}, {"Category 1", "Handeld category"}})
in
    #"Renamed Columns"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers