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.
I have an issue with changing my primary data source query. I want to add a step to remove the first row then promote the new top row to headers. I have tried several diffrent ways and it seems like no matter what I put in it will not take the comands. I have literally coppied and pasted all the data from another query where I did jsut that and have not noticed it change. I have done it to all my other tables in the query but not this one which happens to be the first query.
the syntax is as follows:
let
DATABASE = let
Source = Excel.Workbook(File.Contents("D:\chuck\OneDrive\power BI\PRODUCTION_DATABASE.xlsx"), null, true),
DATABASE_Sheet = Source{[Item="DATABASE",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(DATABASE_Sheet,{{"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 any}, {"Column14", type any}, {"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 any}, {"Column27", type text}, {"Column28", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Promoted Headers" = Table.PromoteHeaders(DATABASE_Sheet),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"AgencyCode", type text}, {"TransactionCode", type text}, {"ProductionMonth", Int64.Type}, {"City", type text}, {"AccountingMonth", Int64.Type}, {"StateCode", type text}, {"BrokerCode", type text}, {"PostalCode", Int64.Type}, {"BrokerName", type text}, {"TransactionAmount", type number}, {"CommissionAmount", type number}, {"AgencyCommissionAmount", type number}, {"AccountName", type text}, {"PolicyTypeDesc", type text}, {"MainPolicyNumber", type text}, {"EffectiveDate", type date}, {"ExpirationDate", type date}, {"AgencyCode_1", type text}, {"ProfitCenterName", type text}, {"City_2", type text}, {"StateCode_3", type text}, {"PostalCode_4", Int64.Type}, {"IssuingCompanyName", type text}, {"PremiumPayableCode", type text}, {"PremiumPayableName", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"CommissionAmount", "Broker Amount"}, {"TransactionAmount", "Production Amount"}, {"AccountingMonth", "Accounting Month"}, {"TransactionCode", "Transaction Code"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"City_2"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"StateCode_3", "State"}, {"ProfitCenterName", "ProfitCenterName"}, {"IssuingCompanyName", "Issuing Company Name"}, {"BrokerName", "Broker Name"}})
in
#"Removed Columns",
In the step where you promote headers, you refer to the original DATABASE_Sheet rather than to the previous step:
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Promoted Headers" = Table.PromoteHeaders(DATABASE_Sheet),
must be
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows"),
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |