Hi Sam
Thanks for responding so quickly. I have attached an image of the data model. Essentially, I have a very much all I need in the MasterFact Table…its probable not ideal but it seems to work OK,
Data modelData model.png876x666 59.2 KB
Advanced Editor of the MasterFact table is as follows:
let
Source = Sql.Database(“srv-sql01\pwc”, “PT_Group_Consol”, [CreateNavigationProperties=false]),
dbo_V_BI_MasterFact = Source{[Schema=“dbo”,Item=“V_BI_MasterFact”]}[Data],
#“Renamed Columns” = Table.RenameColumns(dbo_V_BI_MasterFact,{{“PLSubtotalCategory”, “PLSubtotalCategoryID”}, {“DivisionCode”, “DivisionCode”}, {“AgedCreditor”, “Aged Creditor”}}),
#“Changed Type with Locale” = Table.TransformColumnTypes(#“Renamed Columns”, {{“PeriodStartDate”, type datetime}}, “en-GB”),
#“Changed Type with Locale1” = Table.TransformColumnTypes(#“Changed Type with Locale”, {{“FinancialDate”, type datetime}}, “en-GB”),
#“Renamed Columns1” = Table.RenameColumns(#“Changed Type with Locale1”,{{“WorkingCapitalYTD”, “WorkingCapital”}, {“AgeBracket”, “Aged Debtor/Creditor Age Bracket”}, {“OrderBookType”, “Order Book Type”}, {“Year”, “Financia lYear”}, {“SalesProduct”, “Sales Detail - Product”}, {“SalesCustomer”, “Sales Detail - Customer”}, {“SalesProductCategory”, “Sales Detail - Product Category”}, {“SalesLedgerAdjType”, “Sales Ledger Adj Type”}, {“SalesNumber”, “Sales Detail - Sales Number”}, {“ScenarioDescription”, “Scenario Description”}, {“StockType”, “Stock Type”}, {“WorkingCapitalDays”, “Working Capital Days”}, {“OrderBookKPIType”, “Order Book KPI Type”}, {“OrderBookKPIAmount”, “Order Book KPI Amount”}, {“OrderBookInterGroupFlag”, “Order Book Inter Group Flag”}, {“InterGroupBalanceType”, “Inter Group Balance Type”}, {“InterGroupID”, “Inter Group Subsid ID”}, {“QuotationCount”, “Quotation Count”}, {“QuotationComments”, “Quotation Comments”}, {“QuotationAmount”, “Quotation Amount”}, {“PeriodID”, “Period ID”}, {“ForexBalanceType”, “Forex Balance Type”}, {“ForexCurrency”, “Forex Currency”}, {“GeneralLedgerCode”, “General Ledger Code”}, {“FOBRevenuePeriod”, “FOB Revenue Period”}, {“EntityName”, “Entity Name”}, {“EntityID”, “Entity ID”}, {“EmployeeDepartment”, “Employee Department”}, {“DivisionName”, “Division Name”}, {“DivisionCode”, “Division Code”}, {“CreditTerms”, “Credit Terms”}, {“AgedDebtor”, “Aged Debtor”}, {“AgedDebtorComments”, “Aged Debtor Comments”}, {“AgedCreditorComments”, “Aged Creditor Comments”}})
in
#“Renamed Columns1”
The Budget is based on Called “comparative” and is in the model. I have sent you the PBI separately via email - this will make more sense.
Thank you as always for your prompt responses. I will also take a look at the course information suggested.
Hope to hear from you. Thanks Sam.
