Forum Discussion
Need Urgent help in Power Query
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 ...
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
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
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
instead of replace yo ucan use Fill > Down in the Transform tab
Anonymous Stachu can you please help me with this..
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
