Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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 @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.
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
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
instead of replace yo ucan use Fill > Down in the Transform tab
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |