unpivot changeable date columns from combined tabl...

akhalifaa · ‎12-21-2019

Hello All,

I get data from a folder (different excel files) which is updated every month as the below format, i unpivot the columns to be able to work on the data format

Once i add the next month table the dates

Process	Staff	Employee ID	Hiring Status	Designation	Employee	1-Jan	2-Jan	3-Jan	4-Jan
						PH	P	P	P

Please assist

Jimmy801 · ‎12-21-2019

Hello @akhalifaa

I don't understand your request. I suppose you need a dynamic solution for unpivoting your data? If you like to have this, you need to find a logic how these columns can be identified, or the other way round... identify columns that shouldn't be unpivoted.

Jimmy

akhalifaa · ‎12-21-2019

Here is a sample of the monthly files, However when i add more than 1 file, let's assume that there is name x and 2 months (files) the name x appears 2 times but for the same date 1-Jan, the date doesn't change.

I unpivoted the columns from column 1-Jan till 31 Jan, and every month i add the second file which contains the following month date

Process	Staff	Employee ID	Hiring Status	Designation	Employee	1-Jan	2-Jan	3-Jan	4-Jan	5-Jan
						PH	P	P	P	WO

Process	Staff	Employee ID	Hiring Status	Designation	Employee	1-Feb	2-Feb	3-Feb	4-Feb	5-Feb
						P	WO	AL	AL	AL

Jimmy801 · ‎12-21-2019

Hello @akhalifaa

so you need a dynamic unpivoting that unpivots all otther columsn than this

Process

Staff

Employee ID

Hiring Status

Designation

Employee

This you can achive by using this Syntax

= Table.UnpivotOtherColumns(PreviousStep,{"Process", "Staff", "Employee ID", "Hiring Status", "Designation", "Employee"},"Attribut", "Value")

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

akhalifaa · ‎12-21-2019

Thank you Jimmy,

But i get this error when i apply the DAX formula

Failed to resolve name 'Attendance.UnpivotOtherColumns'. It is not a valid table, variable, or function name.

However Attendance is the name of my table

Jimmy801 · ‎12-21-2019

Hello @akhalifaa

you need to do this in power query, not in power Pivot

so you read the data in power query, cobine the files, apply my function and then pass it to power pivot

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

akhalifaa · ‎12-21-2019

@Jimmy801

I apologize for my limited knowledge as i am new to Power Bi,

But this is what i get when i apply the function

= Table.TransformColumnTypes(#"Unpivoted Columns",{{"Attribute", type date}})
= Table.UnpivotOtherColumns(PreviousStep,{"Process", "Staff", "Employee ID", "Hiring Status", "Designation", "Employee"},"Attribut","Value")

Expression.Error: The name 'PreviousStep' wasn't recognized. Make sure it's spelled correctly.

Jimmy801 · ‎12-21-2019

Hello @akhalifaa

In power query every step has to be handed over to a variable - the part before the equal sign. so to make this work you need something like this

Transform = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Attribute", type date}}),
Unpivot = Table.UnpivotOtherColumns(Transform ,{"Process", "Staff", "Employee ID", "Hiring Status", "Designation", "Employee"},"Attribut","Value")
in
Unpivot

but I'm afraid that it won't work as you are referencing in the first step a variable called #"Unpivoted Columns".. that seems that before is already existing a unpivot-step.

Try to change your code.... if you are not able... post the whole code in the advanced editor and I can fix it

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

akhalifaa · ‎12-21-2019

@Jimmy801

This is the whole code

let
Source = Folder.Files("C:\Users\Khalifa\Downloads\Work\Power Bi\Attendance"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Attendance (6)", each #"Transform File from Attendance (6)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Attendance (6)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Attendance (6)", Table.ColumnNames(#"Transform File from Attendance (6)"(#"Sample File (16)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type any}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"1.xlsx", type text}, {"Process", type text}, {"Staff", type text}, {"Employee ID", type text}, {"Hiring Status", type text}, {"Designation", type text}, {"Employee", type text}, {"1/1/2019", type any}, {"1/2/2019", type any}, {"1/3/2019", type any}, {"1/4/2019", type any}, {"1/5/2019", type any}, {"1/6/2019", type any}, {"1/7/2019", type any}, {"1/8/2019", type any}, {"1/9/2019", type any}, {"1/10/2019", type any}, {"1/11/2019", type any}, {"1/12/2019", type any}, {"1/13/2019", type any}, {"1/14/2019", type any}, {"1/15/2019", type any}, {"1/16/2019", type any}, {"1/17/2019", type any}, {"1/18/2019", type any}, {"1/19/2019", type any}, {"1/20/2019", type any}, {"1/21/2019", type any}, {"1/22/2019", type any}, {"1/23/2019", type any}, {"1/24/2019", type any}, {"1/25/2019", type any}, {"1/26/2019", type any}, {"1/27/2019", type any}, {"1/28/2019", type any}, {"1/29/2019", type text}, {"1/30/2019", type text}, {"1/31/2019", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"1.xlsx", "Process", "Staff", "Employee ID", "Hiring Status", "Designation", "Employee"}, "Attribute", "Value")
in
#"Unpivoted Columns"

Jimmy801 · ‎12-28-2019

Hello @akhalifaa

were you able to handle the issue?

Jimmy

dax · ‎12-22-2019

Hi akhalifaa,

I can't reproduce your design only beased on your code, so if possible, could you please inform me more detailed information(such as your Excel sample, you could try to upload this on forum by one drive or other methods.)? By the way, did you try @Jimmy801 's suggestions?

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.