Need Urgent help in Power Query

[Anonymous]

Hello All,

I have a table as below

Empl No	Emp Name	Day Type	Day 1	Day 2	Day 3
		Weekday	Sunday	Monday	Tuesday
1	a	Day Type	WO	NT	NT
		Time Log
		Total In Time		7.35	0
2	b	Day Type	WO	NT	NT
		Time Log
		Total In Time		1.75	1.75
3	c	Day Type	WO	NT	NT
		Time Log
		Total In Time		8.42	8.87

Here, 

1:- I need to combine Sunday, Monday, Tuesday.. that is first row of the table to Day1, Day2, Day3 column names.

means my columns should look like as

Day1Sunday, Day2Monday, Day3Tuesday and rest are all same.

2:-

For every employee, In day type column there are three values. those are 

Day Type

Timelog

Total In Time

That means the actual table should like as

Empl No	Emp Name	Day Type	Day 1Sunday	Day 2Monday	Day 3Tuesday
1	a	Day Type	WO	NT	NT
1	a	Time Log
1	a	Total In Time		7.35	0
2	b	Day Type	WO	NT	NT
2	b	Time Log
2	b	Total In Time		1.75	1.75
3	c	Day Type	WO	NT	NT
3	c	Time Log
3	c	Total In Time		8.42	8.87

Now how can i get each employee names and emp id, in next two rows where it can have three Day Type values. 

Can anyone please suggest me.

I ll be so thankful to you,

Thanks,

Mohan V

[Anonymous]

HI @Anonymous,

#1, You can simply use transpose function with merged columns function to achieve your requirement:

#2, I think replace function with condition will suitable for this.

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs0tyFHwy1fSAbEU/BJzU4FMl8RKhZDKAhjTEEobQWljpVidaCUgB4jCU1OzUxIrgazg0jwIwzcfyggpTS0GsUCqQWYkohod7g8k/EIgBMLEkMzcVAWf/HQgUwGOkaTzSxJzFDzzFEDqoPLmesamQMoArAzkyiRa2WSoZ24Ko0AqjYGcZFpZZqFnYgSmLMyVYmMB", 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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Empl No", Int64.Type}, {"Emp Name", type any}, {"Day TypeWeekday", type text}, {"Day 1Sunday", type text}, {"Day 2Monday", type text}, {"Day 3Tuesday", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Empl No", "Empl No"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,each Function.Invoke((index as number,tb as table)=> Table.SelectRows(tb, each [Index]< index and [Empl No]<> null)[Empl No]{0},{[Index],#"Renamed Columns"}),Replacer.ReplaceValue,{"Empl No"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","",each Function.Invoke((index as number,tb as table) as text => Table.SelectRows(tb, each [Index]< index and ([Emp Name] <> "" or [Emp Name] <> null))[Emp Name]{0},{[Index],#"Replaced Value"}),Replacer.ReplaceValue,{"Emp Name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value1",{"Index"})
in
    #"Removed Columns"

Result:

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks for the reply @Anonymous.

Appreciate for your help.

I tried what you have provided.

But its replacing all the null values with "a" only.

Output that i got.

I would like to get the output as i mentioned in question that

Empl No	Emp Name	Day Type	Day 1	Day 2	Day 3	Day 4	Day 5	Day 6
1	a	Day Type	WO	NT	NT	TR	NT	NT
		Time Log
		Total In Time	7.35	0		0	2.93
2	b	Day Type	WO	NT	NT	AB	AB	AB
		Time Log
		Total In Time	1.75	1.75
3	c	Day Type	WO	NT	NT	AB	NT	NT
		Time Log
		Total In Time	8.42	8.87		3.64	6.18
4	d	Day Type	WO	PR	PR	PR	PR	LV
		Time Log		9	9	9	9
		Total In Time	6.63	3.13	6.45	4.71

Actually For each employee should have three values in Day Type column, which are Day Type, Timelog, Total In Time 

But which is not in current table.

So i would like to get the table as 

Empl No	Emp Name	Day Type	Day 1	Day 2	Day 3	Day 4	Day 5	Day 6
1	a	Day Type	WO	NT	NT	TR	NT	NT
1	a	Time Log
1	a	Total In Time		7.35	0		0	2.93
2	b	Day Type	WO	NT	NT	AB	AB	AB
2	b	Time Log
2	b	Total In Time		1.75	1.75
3	c	Day Type	WO	NT	NT	AB	NT	NT
3	c	Time Log
3	c	Total In Time		8.42	8.87		3.64	6.18
4	d	Day Type	WO	PR	PR	PR	PR	LV
4	d	Time Log		9	9	9	9
4	d	Total In Time		6.63	3.13	6.45	4.71

Can you please help me for this..

Thanks,

Mohan V

[Anonymous]

@Anonymous @Stachu can you please help me with this..

[Anonymous · Accepted solution]

HI @Anonymous,

I replace 'replace value' part with @Stachu 's suggestion:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs0tyFHwy1fSAbEU/BJzU4FMl8RKhZDKAhjTEEobQWljpVidaCUgB4jCU1OzUxIrgazg0jwIwzcfyggpTS0GsUCqQWYkohod7g8k/EIgBMLEkMzcVAWf/HQgUwGOkaTzSxJzFDzzFEDqoPLmesamQMoArAzkyiRa2WSoZ24Ko0AqjYGcZFpZZqFnYgSmLMyVYmMB", 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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Empl No", Int64.Type}, {"Emp Name", type text}, {"Day TypeWeekday", type text}, {"Day 1Sunday", type text}, {"Day 2Monday", type text}, {"Day 3Tuesday", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Empl No", "Empl No"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Index"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","",null,Replacer.ReplaceValue,{"Emp Name"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Empl No", "Emp Name"})
in
    #"Filled Down"

Regards,

Xiaoxin Sheng

[Stachu]

instead of replace yo ucan use Fill > Down in the Transform tab