Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

 

 

1 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
Community Support

HI @Anonymous,

 

#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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

Anonymous
Not applicable

@v-shex-msft @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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.