Create multiple rows from a single row

[caracallynx]

I have been given a spreadsheet containing a table; each row of the table represents the weekly budget for sectors and departments within the business. Below is an example of the data (including header rows):

	SECTOR 1				SECTOR2
WEEK ENDING	D1	D2	D3	D4	D1	D2	D3	D4
8/3/2020	2400	1600	3000	0	2500	2700	1400	500
15/3/2020	2600	1800	3200	200	2700	2900	1600	700

For each week of budget data, a new row is required for each sector and each department within the sector (one input row will produce 8 output rows); for example:

LOCATION	WEEK ENDING	SECTOR	DEPT	BUDGET
H01*	8/3/2020	SECTOR1*	D1*	2400
H01*	8/3/2020	SECTOR1*	D2*	1600
H01*	8/3/2020	SECTOR1*	D3*	3000
H01*	8/3/2020	SECTOR1*	D4*	0
H01*	8/3/2020	SECTOR2*	D1*	2500
H01*	8/3/2020	SECTOR2*	D2*	2700
H01*	8/3/2020	SECTOR2*	D3*	1400
H01*	8/3/2020	SECTOR2*	D4*	500

* These fields would contain fixed text.

Can this be achieved using Power Query and, if so, how?

I am a complete newbie to Power Query; any help would be much appreciated.

Thank you in advance.

Michael

[HotChilli · Accepted solution]

Welcome to the forum.

What a way to dive in.  I imagine this is example is going to cause a few headaches but here goes.

You have to get your spreadsheet into Power Query first.  Then a series of transforms should get you what you want.

Here's what it looks like on the first import:

Here is the advanced editor code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY3BCoAgDIZfRTwL6dSyc5OIwKCCDuL7v0bOXQw6bP/4tv9fzlIqecXlPk5h6ii6YgwfWlSWT4y7iAm3tFaGZEO6QkvN/TLyhcEOoEFXAk6TmLGJ1U3awrcRJt7zGTEKML5LYKsJnABs680w9z+IlfIC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"WEEK ENDING", type text}, {"8/3/2020", Int64.Type}, {"15/3/2020", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"", "SECTOR"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns"," ",null,Replacer.ReplaceValue,{"SECTOR"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"SECTOR"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Filled Down",{{"WEEK ENDING", "DEPT"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns1", {"SECTOR", "DEPT"}, "Attribute", "Value"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Attribute", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"SECTOR", Order.Ascending}, {"Attribute", Order.Ascending}, {"DEPT", Order.Ascending}})
in
    #"Sorted Rows"

If you import the Excel spreadsheet, you should get to what is in the picture.

If you paste the code above into the advanced editor (except the "Source = " line - because that will be different at your side), you'll be off to a good start.

Let me know how you get on..

[vurej]

I have  data set like this now I need to split the rows for each of the rows below doing Days/Total Hours

My data comes from a database table and this(Below table) is how it looks from database but what I need is when user selects one row from power BI report it should display another table with corresponding rows result set should be:

A1234      1.0    0.4 

A1234      1.0    0.4 

A1234      1.0    0.4 

Basically this one rwo should transpose to 100 Rows since it has 100 days

Proj ID	WBS ID	Total Hours	Days
A1234	1.0	40	100
A1234	1.1	50	150
A5678	1.0	60	60
A5678	1.1	70	100
B1234	1.0	80	1000

[HotChilli · Accepted solution]

Welcome to the forum.

What a way to dive in.  I imagine this is example is going to cause a few headaches but here goes.

You have to get your spreadsheet into Power Query first.  Then a series of transforms should get you what you want.

Here's what it looks like on the first import:

Here is the advanced editor code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY3BCoAgDIZfRTwL6dSyc5OIwKCCDuL7v0bOXQw6bP/4tv9fzlIqecXlPk5h6ii6YgwfWlSWT4y7iAm3tFaGZEO6QkvN/TLyhcEOoEFXAk6TmLGJ1U3awrcRJt7zGTEKML5LYKsJnABs680w9z+IlfIC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"WEEK ENDING", type text}, {"8/3/2020", Int64.Type}, {"15/3/2020", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"", "SECTOR"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns"," ",null,Replacer.ReplaceValue,{"SECTOR"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"SECTOR"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Filled Down",{{"WEEK ENDING", "DEPT"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns1", {"SECTOR", "DEPT"}, "Attribute", "Value"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Attribute", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"SECTOR", Order.Ascending}, {"Attribute", Order.Ascending}, {"DEPT", Order.Ascending}})
in
    #"Sorted Rows"

If you import the Excel spreadsheet, you should get to what is in the picture.

If you paste the code above into the advanced editor (except the "Source = " line - because that will be different at your side), you'll be off to a good start.

Let me know how you get on..

[caracallynx]

@HotChilli 

Thank you so much for your help. I works perfectly. 

In anticipation of a request from our Chief Accountant, would it be possible, once the rows have been created as per your code, to then create a daily budget record for each day of the week? For example:

LOCATION	WEEK ENDING	SECTOR	DEPT	BUDGET
H01*	8/3/2020	SECTOR1*	D1*	2400

Generates 7 new daily rows

LOCATION	WEEK ENDING	SECTOR	DEPT	BUDGET
H01	2/3/2020	SECTOR1	D1	342.86
H01	3/3/2020	SECTOR1	D1	342.86
H01	4/3/2020	SECTOR1	D1	342.86
H01	5/3/2020	SECTOR1	D1	342.86
H01	6/3/2020	SECTOR1	D1	342.86
H01	7/3/2020	SECTOR1	D1	342.86
H01	8/3/2020	SECTOR1	D1	342.86

Thank you in advance.

Regards,

Michael

[HotChilli · Accepted solution]

If you start with the row you provided as being the initial table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jAwVNJRstA31jcyMDIAMoNdnUP8g0CCLiDCyMTAQCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [LOCATION = _t, #"WEEK ENDING" = _t, SECTOR = _t, DEPT = _t, BUDGET = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LOCATION", type text}, {"WEEK ENDING", type date}, {"SECTOR", type text}, {"DEPT", type text}, {"BUDGET", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "WeekBeforeDate", each Date.AddDays( [WEEK ENDING],  -6)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"WeekBeforeDate", type date}}),
    DaysBetween = Table.AddColumn( #"Changed Type1", 
        "DaysBetweenList", 
        (row) =>
            List.Generate(
                () => (row[WeekBeforeDate]) ,
                each _ <= row[WEEK ENDING],
                each _ + #duration(1, 0, 0, 0) 
            )
    ),
    ExpandList = Table.ExpandListColumn(DaysBetween, "DaysBetweenList"),
    #"Renamed Columns" = Table.RenameColumns(ExpandList,{{"BUDGET", "WeekBUDGET"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "DayBudget", each [WeekBUDGET] / 7)
in
    #"Added Custom1"

[caracallynx]

Wow, thank you so much for all your help today. It is very kind of you.

Now, I need to learn Power Query from the ground up. Does anyone have any book recommendations?

[HotChilli]

well, there's a new book by Gil raviv which I hear is good.

M is for Data Monkey is another

Or Rob collie's book which is a bit older but supposed to be good.

Cheers