Convert Power M query to snowflake view

[DineshArivu]

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]

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]

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

[DineshArivu]

@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:

[AmiraBedh]

@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

[v-dineshya]

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

[v-dineshya]

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

[DineshArivu · Accepted solution]

@v-dineshya  Hey , sorry for late response.

Yeah i got my collegue help to fix this internally.

[v-dineshya]

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