Forum Discussion
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:
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
v-dineshya Hey , sorry for late response.
Yeah i got my collegue help to fix this internally.
8 Replies
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
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 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
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
