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

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.

Reply
Yrstruly2021
Helper V
Helper V

Displaying Rows as defined in M code

I am trying to display rows according to the order i have defined in my M code, problem is it does not display in Matrix the way i want it. I get the option to sort it Asc and Desc.

let
    CashflowDiscriptions = let
    Source = PostgreSQL.Database("aurpostdb-sl-app0002829-devtest1-1.ciodmrm8fjly.us-east-1.rds.amazonaws.com", "IMETA"),
    fact_IMETA_CashflowDiscriptions = Source{[Schema="fact", Item="IMETA_CashflowDiscriptions"]}[Data],
    CustomOrderList = {
        "Cash flow imbalance",
        "Cash flow from ops - management",
        "Cash flow from trading",
        "Profit before Brambles allocations Total",
        "Depreciation and amortisation",
        "IPEP expense",
        "Disposals & impairment of fixed assets",
        "Profit on disposal of pooling equipment",
        "Scrapped pooling equipment",
        "Impairment or valuation adjustment of pooling equipment",
        "Disposals or valuation adjustments of other fixed assets",
        "Other cash flow from trading adjustments",
        "Share-based payments expense",
        "JV adjustment",
        "JV share of PAT",
        "JV dividends received",
        "Pension plan adjustment",
        "Trading cash flow adjustments",
        "Working capital mvts incl. provisions",
        "Working capital mvts excl. provisions",
        "Debtor movements",
        "Creditor movements",
        "Inventory movements",
        "Prepayment movements",
        "Other asset movements",
        "Provision movements",
        "Dilapidation provision paid",
        "Working capital - budget flex",
        "Investing cash flows (excl. M&A activity)",
        "Capital expenditure",
        "Purchases of PP&E",
        "Pooling equipment acquired",
        "Pooling equipment additions",
        "Pooling equipment replacements",
        "Pooling equipment internal transfers",
        "Other PP&E acquired",
        "Other PP&E additions",
        "Other PP&E replacements",
        "Other PP&E internal transfers",
        "Change in capex creditors",
        "Lease adj - No longer used",
        "Pooling equipment internal tfr POSA adj",
        "Purchases of intangible assets",
        "Software intangibles acquired",
        "Other intangibles acquired",
        "Deferred expenditure",
        "Investments made",
        "Investments acquired",
        "Joint venture loans",
        "Capital expenditure cash flow adjustments",
        "Capital expenditure - budget flex",
        "Proceeds from disposals",
        "Proceeds from disposals of PP&E",
        "Proceeds pooling disposals",
        "WDV pooling equip. disposals & write-offs",
        "Gain pooling equip. disposals & write-offs",
        "Proceeds other PP&E disposals",
        "WDV other PP&E disposals",
        "Profit other PP&E disposals",
        "Change in loss compensation balances",
        "Change in deferred fixed asset proceeds",
        "Proceeds disposals of intangible assets",
        "Proceeds software intangibles disposals",
        "WDV software intangibles disposals",
        "Profit software intangibles disposals",
        "Proceeds other intangibles disposals",
        "WDV other intangibles disposals",
        "Profit other intangibles disposals",
        "Proceeds deferred expenditure disposals",
        "WDV deferred expenditure disposals",
        "Profit deferred expenditure disposals",
        "Proceeds investment disposals",
        "WDV investments disposals",
        "Profit investments disposals",
        "Disposal proceeds cash flow adjustments",
        "Brambles allocations not in mgt cash flow",
        "Cash flow from ops - statutory adjustment",
        "Cash flow from ops - statutory",
        "Net interest paid",
        "Interest received",
        "Interest revenue",
        "Change in interest receivable",
        "Interest received cash flow adjustments",
        "Interest paid",
        "Interest expense Total",
        "Interest expense not impacting cash flow",
        "Amortisation of upfront fees",
        "Lease interest",
        "Discount unwind on long term provisions",
        "FX on foreign currency denominated debt",
        "FX on interco debt",
        "Finance expense capitalised",
        "Derivatives - FX forward revaluation",
        "Hedges of foreign currency interco debt",
        "Interest rate swaps FV adj. cash flow hedges",
        "Interest rate swaps FV hedges",
        "Interco interest and guarantee fees",
        "Bond fair value adjustment",
        "Interest cash flow not impacting expense",
        "Change in interest payable",
        "Interest paid on other debt instruments",
        "Interest paid on leases",
        "Interest portion of financial instruments settled",
        "Interest paid cash flow adjustments",
        "Net tax paid",
        "Tax paid",
        "Tax refunded",
        "Fiscal unity tax transfers",
        "Tax cash flow adjustments",
        "Free cash flow before dividends",
        "Dividends paid",
        "Dividends paid to Brambles shareholders",
        "Dividends paid to minorities",
        "Dividend cash flow adjustments",
        "Free cash flow after dividends",
        "Entity acquisitions and disposals",
        "Entity acquisitions",
        "Total purchase price of entities acquired",
        "Purchase price of entities acquired",
        "Cash price of entities acquired",
        "Non-cash consideration in purchase price",
        "Costs directly incurred on entity acquisitions",
        "Deferred settlement of entity acquisitions",
        "Deferred settlements now paid",
        "Non-cash consideration for entity acquisitions",
        "Net cash held by entity at date of acquisition",
        "Entity acquisition cash flow adjustments",
        "Proceeds from entity disposals",
        "Net price of entities disposed",
        "Agreed sale price of entities disposed",
        "Cash price of entities disposed",
        "Non-cash consideration in agreed sale price",
        "Costs directly incurred on entity disposals",
        "Provisions raised on entity disposals",
        "Net change disposal provisions and accruals",
        "Deferred settlement of entity disposals",
        "Deferred settlements now received",
        "Non-cash consideration for entity disposals",
        "Net cash held by entity at date of disposal",
        "Entity disposal cash flow tax adjustments",
        "Entity disposal cash flow other adjustments",
        "Other cash flow items",
        "Net equity raised by Brambles Limited",
        "Cash share issues by Brambles Limited",
        "Cost sharing payments to Brambles Limited",
        "Cash paid for share buyback",
        "Equity raised cash flow adjustment",
        "FX adjustments to cash flow",
        "Interco cash flows",
        "Interco interest and guarantee fees",
        "Interco royalties",
        "Statutory reallocations",
        "Cashflow imbalance",
        "Interco dividends Total",
        "Change in interco balances",
        "Change in interco recharge clearing",
        "FX on interco debt",
        "Interco capital issued",
        "Interco capital returned",
        "Cash balances transfer on merging entities",
        "Interco cash flow adjustments",
        "Cashflow budget flex adjustment",
        "Financial instruments principal settled",
        "Net debt increase/(decrease)",
        "Loans drawn down",
        "Loan principal repaid",
        "Leases principal paid",
        "Change in cash net of overdraft"
    },
    ReorderedColumns = Table.AddColumn(fact_IMETA_CashflowDiscriptions, "CustomOrder", each List.PositionOf(CustomOrderList, [description], 0)),
    SortedTable = Table.Sort(ReorderedColumns, {{"CustomOrder", Order.Ascending}})
in
    Table.RemoveColumns(SortedTable, "CustomOrder")
in
    CashflowDiscriptions

Yrstruly2021_0-1706813953684.pngYrstruly2021_1-1706813989123.png

 

Please assist?

1 REPLY 1
Greg_Deckler
Super User
Super User

@Yrstruly2021 Try adding an Index in Power Query and then using that as your Sort By field in Desktop.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors