Reply
Highlighted
Member
Posts: 204
Registered: ‎02-14-2017
Accepted Solution

Need Urgent help in Power Query

Hello All,

 

I have a table as below

 

Empl NoEmp NameDay TypeDay 1Day 2Day 3
  WeekdaySundayMondayTuesday
1aDay TypeWONTNT
  Time Log   
  Total In Time 7.350
2bDay TypeWONTNT
  Time Log   
  Total In Time 1.751.75
3cDay TypeWONTNT
  Time Log   
  Total In Time 8.428.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 NoEmp NameDay TypeDay 1SundayDay 2MondayDay 3Tuesday
1aDay TypeWONTNT
1aTime Log   
1aTotal In Time 7.350
2bDay TypeWONTNT
2bTime Log   
2bTotal In Time 1.751.75
3cDay TypeWONTNT
3cTime Log   
3cTotal In Time 8.428.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

 

 

 


Accepted Solutions
Community Support Team
Posts: 5,401
Registered: ‎08-14-2016

Re: Need Urgent help in Power Query

HI @MohanV,

 

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

View solution in original post


All Replies
Community Support Team
Posts: 5,401
Registered: ‎08-14-2016

Re: Need Urgent help in Power Query

HI @MohanV,

 

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

14.gif

 

 

#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:

19.PNG

 

 

Regards,

Xiaoxin Sheng

Member
Posts: 204
Registered: ‎02-14-2017

Re: Need Urgent help in Power Query

[ Edited ]

Thanks for the reply @v-shex-msft.

Appreciate for your help.

I tried what you have provided.

 

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

 

Output that i got.

1.PNG

 

 

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

Empl NoEmp NameDay TypeDay 1Day 2Day 3Day 4Day 5Day 6
1aDay TypeWONTNTTRNTNT
  Time Log      
  Total In Time7.350 02.93
2bDay TypeWONTNTABABAB
  Time Log      
  Total In Time1.751.75   
3cDay TypeWONTNTABNTNT
  Time Log      
  Total In Time8.428.87 3.646.18
4dDay TypeWOPRPRPRPRLV
  Time Log 9999 
  Total In Time6.633.136.454.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 NoEmp NameDay TypeDay 1Day 2Day 3Day 4Day 5Day 6
1aDay TypeWONTNTTRNTNT
1aTime Log      
1aTotal In Time 7.350 02.93
2bDay TypeWONTNTABABAB
2bTime Log      
2bTotal In Time 1.751.75   
3cDay TypeWONTNTABNTNT
3cTime Log      
3cTotal In Time 8.428.87 3.646.18
4dDay TypeWOPRPRPRPRLV
4dTime Log 9999 
4dTotal In Time 6.633.136.454.71 

 

Can you please help me for this..

 

Thanks,

Mohan V

Established Member
Posts: 170
Registered: ‎06-23-2016

Re: Need Urgent help in Power Query

instead of replace yo ucan use Fill > Down in the Transform tabCapture.PNG

Member
Posts: 204
Registered: ‎02-14-2017

Re: Need Urgent help in Power Query

@v-shex-msft @Stachu can you please help me with this..

Community Support Team
Posts: 5,401
Registered: ‎08-14-2016

Re: Need Urgent help in Power Query

HI @MohanV,

 

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