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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
DineshArivu
Helper I
Helper I

Convert Power M query to snowflake view

Hi Experts,

 

We want to create a view in snowflake based on the power bi table data as below:

DineshArivu_0-1756890904671.png

above is the table template, like the same format we have to create a view in snowflake, is there any possibility to use our power bi M query to convert into snowflake view.?
If possible i can able to share my M query. 

We cannot directly create a view in snowflake because power bi table visual has a columns from different tables merged and transformed. so the same step i need to write in snowflake .

Please help me on this asap.

let me know if you need more details

 

Thanks

DK

1 ACCEPTED SOLUTION

@v-dineshya  Hey , sorry for late response.

Yeah i got my collegue help to fix this internally.

View solution in original post

8 REPLIES 8
v-dineshya
Community Support
Community Support

Hi @DineshArivu ,

Thank you for reaching out to the Microsoft Community Forum.

 

As @AmiraBedh mentioned , Please share your Power Query/M code. It will help us to replicate the scenario.

 

Regards,

Dinesh

AmiraBedh
Super User
Super User

Hello can you please share your Power Query/M code?

I will try to help you even if it is Snowflake matter and there is a community dedicated for that 

https://community.snowflake.com/s/groups


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

@AmiraBedh @v-dineshya 
thanks guys and below the one of the table's M query and output :

let
Source = Snowflake.Databases("ensono-envisiondata.snowflakecomputing.com","POWERBI_WH"),
DATALAKE_Database = Source{[Name="DATALAKE",Kind="Database"]}[Data],
DM_BSS_OSS_Schema = DATALAKE_Database{[Name="DM_BSS_OSS",Kind="Schema"]}[Data],
#"Filtered Rows" = Table.SelectRows(DM_BSS_OSS_Schema, each ([Kind] = "View")),
VW_WD_INVOICE_WITHPRODUCTHIERARCHY_View = #"Filtered Rows"{[Name="VW_WD_INVOICE_WITHPRODUCTHIERARCHY",Kind="View"]}[Data],
#"Duplicated Column" = Table.DuplicateColumn(VW_WD_INVOICE_WITHPRODUCTHIERARCHY_View, "SERVICEPERIOD", "SERVICEPERIOD - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "SERVICEPERIOD - Copy", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"SERVICEPERIOD - Copy.1", "SERVICEPERIOD - Copy.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"SERVICEPERIOD - Copy.1", type date}, {"SERVICEPERIOD - Copy.2", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"SERVICEPERIOD - Copy.1", "Service Period From"}, {"SERVICEPERIOD - Copy.2", "Service Period To"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "InvoiceCreationDate_MonthYear", each Date.ToText([INVOICECREATEDDATE], "MMM-yy")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "MergeWID_InvoiceDate", each [CUSTOMERID]&[InvoiceCreationDate_MonthYear]),
#"Sorted Rows" = Table.Sort(#"Added Custom1",{{"CUSTOMERID", Order.Ascending}}),
#"Sorted Rows1" = Table.Sort(#"Sorted Rows",{{"INVOICECREATEDDATE", Order.Ascending}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Sorted Rows1", "INVOICECREATEDDATE", "INVOICECREATEDDATE - Copy"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column1",{{"INVOICECREATEDDATE - Copy", "INVOICECREATEDDATEText"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns1", each Text.Contains([SCHEDULEID], "MRR")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"MergeWID_InvoiceDate", "INVOICECREATEDDATEText", "INVOICECREATEDBY"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Added Custom2" = Table.AddColumn(#"Removed Duplicates", "Custom", each Date.ToText([INVOICECREATEDDATEText], "MMM-dd-yyyy")),
#"Grouped Rows" = Table.Group(#"Added Custom2", {"MergeWID_InvoiceDate"}, {{"All", each _, type table [MergeWID_InvoiceDate=text, INVOICECREATEDDATEText=nullable date, INVOICECREATEDBY=nullable text, Custom=text]}}),
#"Added Custom3" = Table.AddColumn(#"Grouped Rows", "InsertedCreatedDate", each Table.Column([All],"Custom")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom3", {"InsertedCreatedDate", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom4" = Table.AddColumn(#"Extracted Values", "InvoiceCreatedBy", each Table.Column([All],"INVOICECREATEDBY")),
#"Cleaned InvoiceCreatedBy" = Table.TransformColumns(#"Added Custom4", {
"InvoiceCreatedBy", each
let
textValue = Text.Combine(List.Transform(_, Text.From), ","),
nameList = List.Transform(Text.Split(textValue, ","), Text.Trim),
uniqueNames = List.Distinct(nameList)
in
Text.Combine(uniqueNames, ", "),
type text
}),
#"Removed Columns" = Table.RemoveColumns(#"Cleaned InvoiceCreatedBy",{"All"})
in
#"Removed Columns"

Output:

DineshArivu_0-1756906171601.png

 




@DineshArivu  what I understood from you PQ code that you are spliting SERVICEPERIOD into Service Period From/To dates, but later discards them.

You filters rows to only those where SCHEDULEID contains "MRR" and builds a key MergeWID_InvoiceDate = CUSTOMERID + invoice month-year (from INVOICECREATEDDATE, formatted MMM-yy).

Then you removes the duplicates and groups by that key and creates 2 lists seperated by a comma : 1st InsertedCreatedDate the group invoice dates and 2nd InvoiceCreatedBy the list of distinct creators.

 

This is how I imagine the query : 

 

create or replace view DATALAKE.DM_BSS_OSS.VW_MRR_INVOICE_CREATED as
with base as (
  select
      CUSTOMERID,
      cast(INVOICECREATEDDATE as date) as INVOICECREATEDDATE,
      INVOICECREATEDBY,
      SCHEDULEID
  from DATALAKE.DM_BSS_OSS.VW_WD_INVOICE_WITHPRODUCTHIERARCHY
  where SCHEDULEID ilike '%MRR%'
),
dedup as (  
  select distinct
      (CUSTOMERID || to_char(INVOICECREATEDDATE, 'Mon-YY')) as MERGEWID_INVOICEDATE,
      INVOICECREATEDDATE,
      INVOICECREATEDBY
  from base
)
select
    MERGEWID_INVOICEDATE,
    listagg(to_char(INVOICECREATEDDATE, 'Mon-DD-YYYY'), ',')
      within group (order by INVOICECREATEDDATE asc) as INSERTEDCREATEDDATE,
    listagg(distinct trim(INVOICECREATEDBY), ', ') as INVOICECREATEDBY
from dedup
group by MERGEWID_INVOICEDATE

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Hi @DineshArivu ,

Could you please try the proposed solution shared by @AmiraBedh . Please do let us know if you have any further queries.

 

Regards,

Dinesh

Hi @DineshArivu ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

 

Regards,

Dinesh

@v-dineshya  Hey , sorry for late response.

Yeah i got my collegue help to fix this internally.

Hi @DineshArivu ,

Thank you for the update. We are pleased to hear that you have found a workaround. Please share the details here, to assist others with similar issues in community.  Please do let us know if you have any further queries. 

 

Regards,

Dinesh

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Kudoed Authors