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