Power BIのPower Queryで環境変数を取得する方法

Power Queryで、読み込みファイルに（相対パスではなく）絶対パスを要求する場面があるが、Power Query M言語では環境変数にアクセスできないように見える。
なので、Pythonを利用して環境変数をとってみる。
まず、何とかして、以下を実行できるようにしてみる。
https://learn.microsoft.com/ja-jp/power-bi/connect-data/desktop-python-in-query-editor

私の環境では、pip install pandasだけでは動かなくて、pip install matplotlibもしてみた。


そのあと、PowerQueryで適当な名前のクエリを作り以下の内容にしてみた。


let
    ソース =#table({"a"},{{0}}),
    PyExe = Python.Execute("dataset[""name""]=os.environ['USERPROFILE']",[dataset=ソース]),
    ExpandTable = Table.ExpandTableColumn(PyExe, "Value", {"name"}, {"name.1"}),
    ExpandValue =ExpandTable{0}[name.1]
in
    ExpandValue


解説
Python.Executeでは、テーブルの入力が必要らしいので、ソースに、ダミーのテーブル(列名がaで、中身が0の1行レコードのテーブル)を入れる。
ダミーのテーブルに対して、name列を追加して、その内容を環境変数USERPROFILEの展開後のものとした。
PythonのテーブルがValueに入るため、Valueのテーブル内にアクセスできるように展開した。
展開後のテーブルの1行目、name.1列の内容をExpandValueとし、出力結果をExpandValueとした。


洗練されてはおらずいろいろとゴミがあるが、
これで、このクエリの結果がUSERPROFILEの展開後の結果となった。

PowerShellでの配列の中のオブジェクトアクセス

以下試してみた結果、PowerShellの配列を正しく指定しなかった場合でもPowerShellが裏で仕事をして、いい感じにデータを採取してくれるみたい。 ただ更新の場合は、オブジェクト位置を正しく示している場合のみ、更新できるみたい。 正しく位置指定をしなかった場合、配列の添え字が入れられていないものは配列全体を走査して、見つかったものを返す。複数みつかれば配列として返してくれるみたい。だけど、当然というか、PowerShellが裏で仕事して引っ張ってきたデータについては、更新がエラーになったり無効となったりしている。 データ構造の理解があいまいだと、値書き換え時に大変なことになりそうな予感がする。 まあ、データ構造を完全把握していれば、データベース的に検索ができそうなきがするが。。。

PS > $a=((1,2,@{a=10;b=11}),(4,5,@{a=20}),(7,8,@{a=30;b=12}))
PS > $a.a
10
20
30
PS > $a.b
11
12
PS > $a.a.length
3
PS > $a.b.length
2
PS > $a.a[0]
10
PS > $a.a[0]=100
PS > $a.a[0]
10
PS > $a[0].a=100
このオブジェクトにプロパティ 'a' が見つかりません。プロパティが存在し、設定可能であることを確認してください。
発生場所 行:1 文字:1
+ $a[0].a=100
+ ~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) []、RuntimeException
    + FullyQualifiedErrorId : PropertyAssignmentException

PS > $a.a
10
20
30
PS > $a[0][2].a
10
PS > $a[0][2].a=100
PS > $a.a
100
20
30
PS > $a.b[1]
12
PS > $a[1].b
PS > $a[0].b
11
PS > $a[2].b
12
PS > $a[2].b=120
このオブジェクトにプロパティ 'b' が見つかりません。プロパティが存在し、設定可能であることを確認してください。
発生場所 行:1 文字:1
+ $a[2].b=120
+ ~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) []、RuntimeException
    + FullyQualifiedErrorId : PropertyAssignmentException

PS > $a.b[1]
12
PS > $a[1].b
PS > $a.b[1]
12
PS > $a.b[1]=120
PS > $a.b
11
12
PS > $a[2][2].b
12
PS > $a[2][2].b=120
PS > $a.b
11
120
PS >

このほか、forなどループ文にたいしても=で結果を変数に代入できるけれど、これはパイプと同じくループ文の塊の中で出力した結果が変数に入るみたい。 使いどころはあるのだけれど、powershellは癖が強いなぁ。 そういえば、メイン？で使っていたブログがサービス終了となっていましたので、つぶやきはこちらだけになりそうです。

DBの1対多の検索（アプリ側からの指定）

１対多の関係の属性値について、アプリの検索画面で、andとかorとか括弧とか使った検索をさせる場合にどういうようなテーブル設計にして、どのように検索させれば良いかということについて、とっさに何も思い浮かばなかったので、ちょっと考えてみた。

id1の値が2～10,id2がid1の倍数で最大12まであるゼロ埋め２桁のもの。
検索条件で、+をor,*をand、括弧が使えるもの。
で、id2の検索条件からid1を検索する場合、
条件が"04+06"で、結果が"2,3,4,6",
条件が"04*06"で、検索結果が、"2",
条件が"(06+08)*10"で、結果が、"2",
条件が"06+08*10"で、結果が、"2,3,5,6"
条件が"04*08*12"で、結果が、"2,4"
条件が、12*(05+06+(04*08))で、結果が、"2,3,4,6",
条件が、"05+06*(02+03)"で、id1の検索結果が、"2,3,5"

案１
テーブル構造:tbl(id1,id2)下線は主キー
データ：(2,'02_04_06_08_10_12'),(3,'03_06_09_12'),(4,'04_08_12'),(5,'05_10'),(6,'06_12'),(7,'07'),(8,'08'),(9,'09'),(10,'10')
sql文：
先頭部分は、固定で"select id1 from tbl where "
末尾に動的に追加、
条件が、値の場合、"id2 like '%値%'"　を追加し、次の条件を読み取る。
条件が、"+"の場合、" or "を追加し、次の条件を読み取る。
条件が、"*"の場合、" and "を追加し、次の条件を読み取る。
条件が、"-"の場合、次の値まで読んで、id not like '%値%'"を追加し、次の条件を読み取る。
条件が、括弧の場合は、そのまま追加し、次の条件を読み取る。

案２
テーブル構造:tbl(id1,id2) 下線は主キー,インデックスの列の指定順はid2,id1の順
データ:(2,'02'),(2,'04'),(2,'06'),(2,'08'),(2,'10'),(2,'12'),(3,'03'),(3,'06'),(3,'09'),(3,'12'),(4,'04'),(4,'08'),(4,'12'),(5,'05'),(5,'10'),(6,'06'),(6,'12'),(7,'07'),(8,'08'),(9,'09'),(10,'10')
sql文：
固定先頭部分はなしで、
sql文を動的に追加、
条件が、値の場合、"select id1 from tbl where id2='値' "を追加し、次の条件を読み取る。
条件が、"+"の場合、"union "を追加し、次の条件を読み取る。
条件が、"*"の場合、"intersect "を追加し、次の条件を読み取る。
条件が、"-"の場合、"except "を追加し、次の条件を読み取る。
条件が、括弧の場合は、そのまま追加し、次の条件を読み取る。

で、いいのかな？

プロコン２０１２

今年のプロコン、androidアプリが題材のがある。 android端末が2つ持ってるので参加してみたいんだけど、今年は期限内に完成まで漕ぎ着ける見込みが立たないので今回も不参加。 ま、勉強がてら期限は設定せずにプロコンと同じ題材で取り組んでみようかな？

プロコン2010-1その8

ユーザの削除画面。
ユーザを削除すると、そのユーザに紐づく書籍を他の人に移管するかシステムから削除するようにするため、ちょっと考慮が必要でした。


で、DACは、前回書いたUserInfo.csなので今回は省略して、
UI(~/Admin/DeleteUser.aspx)から

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DeleteUser.aspx.cs" Inherits="Library.Admin.DeleteUser"
    MasterPageFile="~/Admins.Master" StylesheetTheme="Standard" %>

<asp:Content ID="Content1" runat="server" ContentPlaceHolderID="ContentPlaceHolder1">
    <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
    <br />
    削除対象のユーザを選択してください。<asp:RadioButtonList ID="RadioButtonList1" runat="server" DataSourceID="ObjectDataSource1"
        DataTextField="UserName" DataValueField="UserName">
    </asp:RadioButtonList>
    <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetAllData"
        TypeName="Library.DAC.UserInfo"></asp:ObjectDataSource>
    <br />
    書籍の引継ぎユーザを指定してください。<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="ObjectDataSource1"
        DataTextField="UserName" DataValueField="UserName" AppendDataBoundItems="True">
        <asp:ListItem Value="">引き継ぎ無し</asp:ListItem>
    </asp:DropDownList>
    <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
</asp:Content>

次にコードビハインド

using System;
using System.Web.Security;
using System.Web.UI;
using Library.DAC;

namespace Library.Admin
{
    public partial class DeleteUser : LibPage
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Label1.Text = "";
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            Membership.DeleteUser(RadioButtonList1.SelectedValue);
            UserInfo.InheritBookOwner(RadioButtonList1.SelectedValue, DropDownList1.SelectedValue);   
            UserInfo.DeleteUser(RadioButtonList1.SelectedValue);
            Page.DataBind();
            Label1.Text = "削除が完了しました";
        }
    }
}

本当は、ACIDの一貫性を保つために、UserInfo.InheritBookOwner()と、UserInfo.DeleteUser()を連続したよびだしし、トランザクションスコープをくくったUserInfoのメソッドで用意するべきだったけど、まいいかぁ。

プロコン2010-1その7

今回は、ユーザの変更の画面。
DataSetのコードは省略し、DACと、UIのコードを。
まずは、DAC(~/DAC/UserInfo.cs)から。

using System.Collections.Generic;
using System.Web.Security;
using Library.DataSet;
using Library.DataSet.LibraryDataSetTableAdapters;
namespace Library.DAC
{
    public static class UserInfo
    {
        public static LibraryDataSet.UsersDataTable GetAllData()
        {
            LibraryDataSet.UsersDataTable dt = new LibraryDataSet.UsersDataTable();
            using (UsersTableAdapter ta = new UsersTableAdapter())
            {
                dt = ta.GetData();
                foreach (MembershipUser msu in Membership.GetAllUsers())
                {
                    if ((dt.Select("UserName='" + msu.UserName + "'")).Length == 0)
                    {
                        dt.AddUsersRow(msu.UserName, string.Empty, false, false);
                        ta.Update(dt);
                    }
                }
                dt = ta.GetData();
                foreach (LibraryDataSet.UsersRow ur in dt)
                {
                    ur.AdminRoleFlag = Roles.IsUserInRole(ur.UserName, "Admins");
                    ur.UserRoleFlag = Roles.IsUserInRole(ur.UserName, "Users");
                }
                return dt;
            }
        }
        public static void Update(string userName, string information, bool adminRoleFlag, bool userRoleFlag)
            {
            using (UsersTableAdapter ta = new UsersTableAdapter())
            {
                ta.Update(information, userName);
              }
            if (adminRoleFlag && !Roles.IsUserInRole(userName, "Admins"))
            {
                Roles.AddUserToRole(userName, "Admins");
            }
            else if (!adminRoleFlag && Roles.IsUserInRole(userName, "Admins"))
            {
                Roles.RemoveUserFromRole(userName, "Admins");
            }
            if (userRoleFlag && !Roles.IsUserInRole(userName, "Users"))
            {
                Roles.AddUserToRole(userName, "Users");
            }
            else if (!userRoleFlag && Roles.IsUserInRole(userName, "Users"))
            {
                Roles.RemoveUserFromRole(userName, "Users");
            }
        }
        public static void InheritBookOwner(string oldOwner, string newOwner)
        {
            using (BookOwnerTableAdapter ta = new BookOwnerTableAdapter())
            {
                if (string.IsNullOrEmpty(newOwner))
                {
                    ta.DeleteByUserName(oldOwner);
                }
                else
                {
                    ta.InheritBookOwner(newOwner, oldOwner);
                }
            }
        }
        public static void DeleteUser(string userName)
        {
            using (UsersTableAdapter ta = new UsersTableAdapter())
            {
                ta.Delete(userName);
            }
        }
        public static LibraryDataSet.UsersDataTable GetDataFirstByGivenUser(string GivenUserName)
        {
            using(UsersTableAdapter ta=new UsersTableAdapter())
            {
                return ta.GetDataFirstByGivenUserName(GivenUserName);
            }
        }
    }
}

ユーザ変更のコード部分(~/Admin/ModifyUser.aspx.cs)

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ModifyUser.aspx.cs" Inherits="Library.Admin.ModifyUser"
    MasterPageFile="~/Admins.Master" StylesheetTheme="Standard" %>

<asp:Content ID="Content1" runat="server" ContentPlaceHolderID="ContentPlaceHolder1">
    <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
    <asp:GridView ID="GridView1" runat="server" DataKeyNames="UserName" AutoGenerateColumns="False"
        DataSourceID="ObjectDataSource1" OnRowUpdated="GridView1_RowUpdated" AllowPaging="True"
        AllowSorting="True" CellPadding="4" ForeColor="#333333" GridLines="None">
        <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
        <Columns>
            <asp:CommandField ShowEditButton="True" />
            <asp:BoundField DataField="UserName" HeaderText="ユーザ名" ReadOnly="True" SortExpression="UserName" />
            <asp:BoundField DataField="Information" HeaderText="ユーザ情報" SortExpression="Information" />
            <asp:CheckBoxField DataField="AdminRoleFlag" HeaderText="管理者権限" 
                SortExpression="AdminRoleFlag" />
            <asp:CheckBoxField DataField="UserRoleFlag" HeaderText="ユーザ権限" 
                SortExpression="UserRoleFlag" />
        </Columns>
        <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
        <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
        <AlternatingRowStyle BackColor="White" />
    </asp:GridView>
    <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetAllData"
        TypeName="Library.DAC.UserInfo" UpdateMethod="Update">
        <UpdateParameters>
            <asp:Parameter Name="userName" Type="String" />
            <asp:Parameter Name="information" Type="String" />
            <asp:Parameter Name="adminRoleFlag" Type="Boolean" />
            <asp:Parameter Name="userRoleFlag" Type="Boolean" />
        </UpdateParameters>
    </asp:ObjectDataSource>
</asp:Content>

最後に、コードビハインド

using System;
using System.Web.Security;

namespace Library.Admin
{
    public partial class ModifyUser : LibPage
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Label1.Text = "";
        }

        protected void GridView1_RowUpdated(object sender, System.Web.UI.WebControls.GridViewUpdatedEventArgs e)
        {
            Label1.Text = "更新しました";
        }
    }
}

プロコン2010-1その6

asp.netの開発では、たとえ１人で作る個人的なプログラムであっても
プログラム開発効率を上げるためにある程度の決め事をしておきます。
（まあ設計というほどのものではないですが。）
たとえばこんな感じ。

A)画面デザインと共有パーツやロジックを複数のアセンブリ（DLLやexe）に分けず、1本のアセンブリで作成する。
　→Visual Studioのプロジェクトは1本。
B)プログラムソースコード群を以下のカテゴリに分ける。
・画面デザイン（(UI)１画面内で判断できる画面固有のロジックも含む。フォルダを切らない。）
・ビジネスロジック(DACとBCは分けない。全てDACフォルダに格納する。ファイルアクセスがあればここで行う。)
・データベースアクセス（DBに対するSQL発行。全てDataSetフォルダで行う。）
上記のカテゴリは、隣接するカテゴリのみアクセスを許す。（画面デザインからストレージアクセスへの直呼び出しは不可）
上記のカテゴリのプログラムは、自分より上のカテゴリの構造を前提としてはいけない。（このビジネスロジックを呼び出すためには、呼び出し元がこうなっていなければならないとかはＮＧ）
上記のカテゴリ間の複雑な構造のデータインタフェース(BEC)は、データセットやデータテーブルを用いる。
※データセットやテーブルっていうのは、.netのなかの、ado.netの機能で、データベースやテーブルをメモリ上で実装したものです。
ま、たんに決め事です。

で、データセット作成から、ソリューションエクスプローラで、"DataSet"というフォルダを作って、このフォルダを右クリック、追加でデータセットを作成する。
今回は、Library.xsdという名前で作成しています。
Library.xsdをダブルクリックし、編集画面で、右クリック→追加→DataTableを選択。DataTable1と表示されている部分を左クリックしRolesと変更。
その後、列の追加を行いRoleNameという列を追加。追加した列をクリックし、プロパティ（表示されていなければF4で表示させる。）で、文字列であることを確認する。
これでLibrary.DataSet.LibraryDataSetというクラスの内部クラスで、RolesDataTableクラスが作成される。
なお、Library.DataSet.LibraryDataSetの最初のLibraryは、現プロジェクト名で、次のDataSetは、プロジェクトに作ったフォルダの名前です。
で、RoleDataTableについては、TableAdapterを作っていません。
RoleDataTableへのデータ投入は、ビジネスロジックで行います。
~/DAC/RoleInfo.csの作りは、こんな感じ。これが前回の記事で指定したRoleInfoの内容です。

using System.Web.Security;
using Library.DataSet;

namespace Library.DAC
{
    public static class RoleInfo
    {
        public static LibraryDataSet.RolesDataTable GetAllRole()
        {
            LibraryDataSet.RolesDataTable dt = new LibraryDataSet.RolesDataTable();
            foreach (string s in Roles.GetAllRoles())
            {
                dt.AddRolesRow(s);
            }
            return dt;
        }
    }
}