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
davox01
Regular Visitor

Referencing a column by position not name as name is variable

I'm new to power query and have query running that combines all files in folder, but it errors if i don't include the initial file.

 

It takes all the files from within a folder combines them and transforms and transposes the data. My problem is that one of the columns shows the file names imported and when I promote the top row to a header row, that column doesn't have a fixed header it just has the name of the source file. Which ever source file is imported first ends up having its name as the header for my first column, so any further transformations that i trigger on that column are looking for a specific name. If that file isn't in the import than my transformations will fall over.

 

The issue rears its head at step 10  #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Odigo subscription_2022-05-01.xls", type text}

 

Odigo subscription_2022-05-01.xls becomes a column header is referenced in further steps.  I think I  need to refer to the first column rather than the column by name, but don't knwo if this is possible, or rename it to a fixed name i.e. 'filename' prior to promoting it as a header.

 

 

 

 

let
    Source = Folder.Files("\\customer\shared\Hove\PartnerCustOps\Reporting\SingleReports\TaskTime\Data"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"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 text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Odigo subscription_2022-05-01.xls", type text}, {"", type text}, {"Sign In Duration", type text}, {"Inbound Calls Handled", type text}, {"Inbound Average Handling Time", type text}, {"Talk Time", type text}, {"Wrap-Up Time", type text}, {"On hold Time", type text}, {"Outbound Calls", type text}, {"Outbound Time", type text}, {"Count of Agent Status (Back Office)", type text}, {"Count of Agent Status (Outbound)", type text}, {"Count of Agent Status (Meeting)", type text}, {"Count of Agent Status (Break)", type text}, {"Count of Agent Status (Training)", type text}, {"Count of Agent Status (Wrap-Up)", type text}, {"Count of Agent Status (Task Time)", type text}, {"Count of Agent Status (Lunch)", type text}, {"Count of Agent Status (Agent Transfer)", type text}, {"Count of Agent Status (TU Duties)", type text}, {"Count of Agent Status (Messaging)", type text}, {"Count of Agent status (Inbound Cust Email)", type text}, {"Count of Agent status (Admin/Communications)", type text}, {"Count of Agent status (TaskTime Unscheduled)", type text}, {"Count of Agent status (Medical Back Office)", type text}, {"Count of Agent status (Social Media)", type text}, {"Count of Agent status (Assistance required)", type text}, {"Count of Agent status (Point of Contact)", type text}, {"Count of Agent status (IT Issues)", type text}, {"Count of Agent  status (Complaints)", type text}, {"Number of Logons", type text}, {"Outbound Average Handling Time", type text}, {"Consultation or Transfer", type text}, {"Number of Calls in Fault", type text}, {"Maximum of Agents Log-in", type text}, {"First Logged in", type text}, {"Last Logged off", type text}, {"Total Time:Back Office", type text}, {"Total Time:Outbound", type text}, {"Total Time:Meeting", type text}, {"Total Time:Break", type text}, {"Total Time: Toilet Break", type text}, {"Total Time:Training", type text}, {"Total Time:Lunch", type text}, {"Total Time:Task Time", type text}, {"Total Time:Available Time", type text}, {"Total Time: Agent Transfer", type text}, {"Total Time: TU Duties", type text}, {"Total Time: Messaging", type text}, {"Total Inbound Cust Email", type text}, {"Total Admin/Communications", type text}, {"Total TaskTime Unscheduled", type text}, {"Total Medical Back Office", type text}, {"Total Social Media", type text}, {"Total Assistance required", type text}, {"Total Point of Contact", type text}, {"Total IT Issues", type text}, {"Total Complaints", type text}, {"Outbound Attempts", type text}, {"Sign In Duration (inc. Lunch)", type text}, {"Column61", type any}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"", "Name"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Name", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"Name.1", "Name.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type2",")","",Replacer.ReplaceText,{"Name.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Replaced Value", "Odigo subscription_2022-05-01.xls", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Odigo subscription_2022-05-01.xls.1", "Odigo subscription_2022-05-01.xls.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Odigo subscription_2022-05-01.xls.1", type text}, {"Odigo subscription_2022-05-01.xls.2", type text}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type3",".xls","",Replacer.ReplaceText,{"Odigo subscription_2022-05-01.xls.2"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Replaced Value1",{{"Odigo subscription_2022-05-01.xls.2", "Date"}, {"Odigo subscription_2022-05-01.xls.1", "File"}, {"Name.1", "Name"}, {"Name.2", "ID"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns2", each ([Name] <> null and [Name] <> "" and [Name] <> "Total"))
in
    #"Filtered Rows"

 

 

 

 

 

2 ACCEPTED SOLUTIONS
Vijay_A_Verma
Super User
Super User

1. Insert an Index column just before #"Promoted Headers".

2. Issue this statement (This would change your first row to a column name "Source Name"

Table.ReplaceValue(#"Added Index",each [Source.Name],each if [Index] = 0 then "Source Name" else [Source.Name],Replacer.ReplaceValue,{"Source.Name"})

3. Now delete the Index column

4. After this you can promote headers.

View solution in original post

Thanks I actually used a different solution where i refered to the column by its number  0 = first column.

 

#"Renamed Columns3" = Table.RenameColumns(#"Changed Type1",{{Table.ColumnNames(#"Changed Type1"){0}, "Source"}}),

View solution in original post

2 REPLIES 2
Vijay_A_Verma
Super User
Super User

1. Insert an Index column just before #"Promoted Headers".

2. Issue this statement (This would change your first row to a column name "Source Name"

Table.ReplaceValue(#"Added Index",each [Source.Name],each if [Index] = 0 then "Source Name" else [Source.Name],Replacer.ReplaceValue,{"Source.Name"})

3. Now delete the Index column

4. After this you can promote headers.

Thanks I actually used a different solution where i refered to the column by its number  0 = first column.

 

#"Renamed Columns3" = Table.RenameColumns(#"Changed Type1",{{Table.ColumnNames(#"Changed Type1"){0}, "Source"}}),

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.

Top Solution Authors
Top Kudoed Authors