I am new to power bi please help me on below senario. I have columns like,First Account Grouping,Second Account Grouping,StoreID,Store Loc and Masking Account.By using all columns I want output column (Account # Lable/Identifier) like below green color highlighted.I want exact same result, please suggest with correct concatination process. or any other way or by using new column in DAX, kindly please help on this!Thanks in Advance!

Hi BA_Pete Below is the Excel information. If it is possible in power query please help me with the solution.If not please help me on any other possible way solution. kindly please me on this.Thanks in Advance! First Account GroupingSecond Account GroupingStore IDStore LocMasking AccountAccount # Label/IdentifierRetail Store – Full PriceRetail Ship From Store11abc*****1234Retail Store #11 abc SFS (…1234)Retail Store – Full PriceRetail Ship From Store22efg *****5678Retail Store #22 efg SFS (…5678)Retail Store – Full PriceAll Store Inbound44hig*****0593Retail Store #44 hig Inbound (…0593)Retail Store – OutletAll Store Inbound55klm*****8772Outlet Store #55 klm Inbound (…8772)Retail Store – OutletAll Store Inbound77ikg*****1005Outlet Store #77 ikg Inbound (…1005)Halo Distribution CenterDC Ship To Retail hmmm*****5551Halo DC Ship to Retail (…5551)Halo Distribution CenterDC ECOM Outbound lkll*****3835Halo DC ECOM Outbound (…3835)Hai Distribution CenterDC Zone 1 Direct lllll*****4529Hai DC Zone 1 Direct (…4529)

That should work fine. Check that the column names in your Demo_Data file are EXACTLY the same as those in your example data provided here, and also check that you haven't missed or duplicated any commas at the end of each query step. If you still have issues after this, please paste your exact code here that gives the error using the code window ( </> button). Pete

Multiple columns combine to get exact output as below description attachment, Kindly please help me?

17 Replies

BA_Pete

Super User

3 years ago

Hi Anonymous ,

Can you provide a copyable example of your data please? You can just paste your Excel table straight into a reply.

Also, can you confirm that you want/need thi solution in DAX please? You've posted in the Power Query forum so not sure if there's some confusion here.

Pete

Anonymous

Not applicable

3 years ago

Hi BA_Pete

Below is the Excel information.

If it is possible in power query please help me with the solution.

If not please help me on any other possible way solution. kindly please me on this.

Thanks in Advance!

First Account Grouping	Second Account Grouping	Store ID	Store Loc	Masking Account	Account # Label/Identifier
Retail Store – Full Price	Retail Ship From Store	11	abc	*****1234	Retail Store #11 abc SFS (…1234)
Retail Store – Full Price	Retail Ship From Store	22	efg	*****5678	Retail Store #22 efg SFS (…5678)
Retail Store – Full Price	All Store Inbound	44	hig	*****0593	Retail Store #44 hig Inbound (…0593)
Retail Store – Outlet	All Store Inbound	55	klm	*****8772	Outlet Store #55 klm Inbound (…8772)
Retail Store – Outlet	All Store Inbound	77	ikg	*****1005	Outlet Store #77 ikg Inbound (…1005)
Halo Distribution Center	DC Ship To Retail		hmmm	*****5551	Halo DC Ship to Retail (…5551)
Halo Distribution Center	DC ECOM Outbound		lkll	*****3835	Halo DC ECOM Outbound (…3835)
Hai Distribution Center	DC Zone 1 Direct		lllll	*****4529	Hai DC Zone 1 Direct (…4529)

BA_Pete

Super User

3 years ago

Paste this code into a new blank query using Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZJNTsMwEEavMiobQF3ESYzbJWqpYIGKKCuqLpJgWqtOjIKz7x24AwfrSZiJmao/ilTVi1l4nufl02Q+771qnxkLM+9qDdvND0waa+GlNoXu9XfdlfmCSe3KwGFDCCxZXmC9pSPiJN3j22lXQgAiMJvM4Hq7+SXmprfoXyyNYyz6cwlslXdqcGKNYyCGrcScY7233HqqctdUH3iXUqaVWbIvksPkxJemgAi/ap3EdTinjbfad/ikxLK2JfsGSlHk8IZ9UgIiBz7iLvEphcWsd/lEFMkTn1KAyIGPuOB7zKyDsfn2tckbb1wFI115XeOU8Sis8M1B+Cq8o82tynIXUEpJP1KY8s975sP6kDhH9TCaPlNYjkYmu7aWTckgkXumA7wVEcAi0+15d5UGgf1aF549dFiUynjYigwc462H+uhZ/AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Account Grouping" = _t, #"Second Account Grouping" = _t, #"Store ID" = _t, #"Store Loc" = _t, #"Masking Account" = _t, #"Account # Label/Identifier" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"First Account Grouping", type text}, {"Second Account Grouping", type text}, {"Store ID", Int64.Type}, {"Store Loc", type text}, {"Masking Account", type text}, {"Account # Label/Identifier", type text}}),
    
// Add this column ---->
    addAcctLabelID =
        Table.AddColumn(
            chgTypes,
            "acctLabelID",
            each let
                segm1 = if [Store ID] <> null and Text.Contains([First Account Grouping], "Outlet") then "Outlet Store"
                        else if [Store ID] <> null and not Text.Contains([First Account Grouping], "Outlet") then "Retail Store"
                        else Text.Combine({Text.BeforeDelimiter([First Account Grouping], " "), [Second Account Grouping]}, " "),
                segm2 = if [Store ID] <> null then Text.Combine({"#" & Text.From([Store ID]), [Store Loc]}, " ") else null,
                segm3 = if Text.Contains([Second Account Grouping], "Ship From Store") then "SFS"
                        else if Text.Contains([Second Account Grouping], "All Store Inbound") then "Inbound"
                        else null,
                segm4 = "(..." & Text.End([Masking Account], 4) & ")"
            in
                Text.Combine({segm1, segm2, segm3, segm4}, " ")
        )
        
in
    addAcctLabelID

To get this output:

Pete

Anonymous

Not applicable

3 years ago

What I am importing from Excel is getting correctly in power query. While editing in advance editor getting same error.

Kindly please from your end with importing Excel file with same source code of m language. and please let me know!

I have shared below Excel information and previous shared code. Kindly please and let me know.

Thank you so much for your help!

Thanks in Advance!

First Account Grouping	Second Account Grouping	Store ID	Store Loc	Masking Account	Account # Label/Identifier
Retail Store – Full Price	Retail Ship From Store	11	abc	*****1234	Retail Store #11 abc SFS (…1234)
Retail Store – Full Price	Retail Ship From Store	22	efg	*****5678	Retail Store #22 efg SFS (…5678)
Retail Store – Full Price	All Store Inbound	44	hig	*****0593	Retail Store #44 hig Inbound (…0593)
Retail Store – Outlet	All Store Inbound	55	klm	*****8772	Outlet Store #55 klm Inbound (…8772)
Retail Store – Outlet	All Store Inbound	77	ikg	*****1005	Outlet Store #77 ikg Inbound (…1005)
Halo Distribution Center	DC Ship To Retail		hmmm	*****5551	Halo DC Ship to Retail (…5551)
Halo Distribution Center	DC ECOM Outbound		lkll	*****3835	Halo DC ECOM Outbound (…3835)
Hai Distribution Center	DC Zone 1 Direct		lllll	*****4529	Hai DC Zone 1 Direct (…4529)

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZJNTsMwEEavMiobQF3ESYzbJWqpYIGKKCuqLpJgWqtOjIKz7x24AwfrSZiJmao/ilTVi1l4nufl02Q+771qnxkLM+9qDdvND0waa+GlNoXu9XfdlfmCSe3KwGFDCCxZXmC9pSPiJN3j22lXQgAiMJvM4Hq7+SXmprfoXyyNYyz6cwlslXdqcGKNYyCGrcScY7233HqqctdUH3iXUqaVWbIvksPkxJemgAi/ap3EdTinjbfad/ikxLK2JfsGSlHk8IZ9UgIiBz7iLvEphcWsd/lEFMkTn1KAyIGPuOB7zKyDsfn2tckbb1wFI115XeOU8Sis8M1B+Cq8o82tynIXUEpJP1KY8s975sP6kDhH9TCaPlNYjkYmu7aWTckgkXumA7wVEcAi0+15d5UGgf1aF549dFiUynjYigwc462H+uhZ/AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Account Grouping" = _t, #"Second Account Grouping" = _t, #"Store ID" = _t, #"Store Loc" = _t, #"Masking Account" = _t, #"Account # Label/Identifier" = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"First Account Grouping", type text}, {"Second Account Grouping", type text}, {"Store ID", Int64.Type}, {"Store Loc", type text}, {"Masking Account", type text}, {"Account # Label/Identifier", type text}}),

// Add this column ---->
addAcctLabelID =
Table.AddColumn(
chgTypes,
"acctLabelID",
each let
segm1 = if [Store ID] <> null and Text.Contains([First Account Grouping], "Outlet") then "Outlet Store"
else if [Store ID] <> null and not Text.Contains([First Account Grouping], "Outlet") then "Retail Store"
else Text.Combine({Text.BeforeDelimiter([First Account Grouping], " "), [Second Account Grouping]}, " "),
segm2 = if [Store ID] <> null then Text.Combine({"#" & Text.From([Store ID]), [Store Loc]}, " ") else null,
segm3 = if Text.Contains([Second Account Grouping], "Ship From Store") then "SFS"
else if Text.Contains([Second Account Grouping], "All Store Inbound") then "Inbound"
else null,
segm4 = "(..." & Text.End([Masking Account], 4) & ")"
in
Text.Combine({segm1, segm2, segm3, segm4}, " ")
)

in
addAcctLabelID

BA_Pete

Super User

3 years ago

Ok, now we've got the data expanded, go to the Add Column tab > Custom Column, and paste this into the 'Custom Column Formula' box:

let
    segm1 = if [Store ID] <> null and Text.Contains([First Account Grouping], "Outlet") then "Outlet Store"
            else if [Store ID] <> null and not Text.Contains([First Account Grouping], "Outlet") then "Retail Store"
            else Text.Combine({Text.BeforeDelimiter([First Account Grouping], " "), [Second Account Grouping]}, " "),
    segm2 = if [Store ID] <> null then Text.Combine({"#" & Text.From([Store ID]), [Store Loc]}, " ")
            else null,
    segm3 = if Text.Contains([Second Account Grouping], "Ship From Store") then "SFS"
            else if Text.Contains([Second Account Grouping], "All Store Inbound") then "Inbound"
            else null,
    segm4 = "(..." & Text.End([Masking Account], 4) & ")"
in
    Text.Combine({segm1, segm2, segm3, segm4}, " ")

Pete

Forum Discussion

Multiple columns combine to get exact output as below description attachment, Kindly please help me?

17 Replies

Related content

Attach multiple lakehouse to a single notebook.

Bank Statement, Description Across Multiple Rows

Get email attachment with receivedtime

Observation on attaching multiple Lakehouses to a Fabric notebook

Where are my datasets attached?